Solved

Query in access 97

Posted on 1998-08-29
18
251 Views
Last Modified: 2011-10-03
i have a MY_T table which has 3 fields: F_1,F_2,F_3
i want(with code) to get the values of F_2,F_3 (where F_1 equalls to a number X).
how can i do it?
0
Comment
Question by:rzvika2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
18 Comments
 
LVL 2

Accepted Solution

by:
mkmccreary earned 10 total points
ID: 1960634
Here is some code to do it, I haven't tested it.  I assumed that X was a numeric value.  If it is a string you need to enclose the value in single quotes in the string, and if it is a date it needs to be enclosed within number signs(#).

Dim rsRecords as Recordset
Dim sSQL as String
Dim X as Long

sSQL = "Select F_2, F_3 From MY_T Where F_1 = " & X & ";"
Set rsRecords = DBEngine.Workspaces(0).OpenRecordset(sSQL)
If Not rsRecords.BOF And Not rsRecords.EOF Then
     'There are some records to process
     rsRecords.MoveFirst
     While Not rsRecords.EOF
          ' Process Your Records Here
     Wend
End If
rsRecords.Close
Set rsRecords = Nothing


0
 

Author Comment

by:rzvika2
ID: 1960635
it says:"method or data member not found"
about the: ".OpenRecordset" in the line:
Set rsRecords = DBEngine.Workspaces(0).OpenRecordset(sSQL)

0
 
LVL 2

Expert Comment

by:mkmccreary
ID: 1960636
Sorry,
    A little syntax error there.  The line should read

    Set rsRecords = DBEngine.Workspaces(0).Databases(0).OpenRecordset(sSQL)

Try this and see if it work.

Martin
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:rzvika2
ID: 1960637
well, the final cade(that the access was agree to do)is as followed:

    sSQL = "SELECT T_DefaultSpecialAngles.CodeAngle,    T_DefaultSpecialAngles.DegreeAngle, T_DefaultSpecialAngles.Convert FROM T_DefaultSpecialAngles Where T_DefaultSpecialAngles.CodeAngle = " & CAngle & ";"
    Set rsRecords = DBEngine.Workspaces(0).Databases(0).OpenRecordset(sSQL)
    If Not rsRecords.BOF And Not rsRecords.EOF Then
         rsRecords.MoveFirst
         While Not rsRecords.EOF
            a = rsRecords!Convert
            b = rsRecords!DegreeAngle
            c = rsRecords!CodeAngle
            rsRecords.MoveNext
         Wend
    End If
    rsRecords.Close
    Set rsRecords = Nothing

(the only missed thing was the  "rsRecords.MoveNext")
thank you!
(by the way, if you tell me the actions that i have to do in order to play wave and avi files by access, i'll give you more 40 points).
and thank you again.

0
 
LVL 2

Expert Comment

by:mkmccreary
ID: 1960638
Here you go.  If in Access 97 open the form you wish to play the files from in design mode.  You then need to use the Insert menu and select ActiveX Controls.  Select the 'Microsoft MCI Control'.  You then need to add the below code from where you want the playing to occur:


    ' Initialize the control
    MMControl1.Visible = False
    MMControl1.Enabled = False
    MMControl1.Notify = False
    MMControl1.Wait = True
    MMControl1.Shareable = False
   
    ' Play a wave file
    MMControl1.DeviceType = "WaveAudio"
    MMControl1.Filename = "D:\WINNT\Media\chimes.WAV"
    MMControl1.Command = "Open"
    MMControl1.Wait = True
    MMControl1.Command = "Play"
    MMControl1.Command = "Close"
   
    ' Play an AVI file
    MMControl1.DeviceType = "AVIVideo"
    MMControl1.Filename = "D:\WINNT\clock.avi"
    MMControl1.Command = "Open"
    MMControl1.Wait = True
    MMControl1.Command = "Play"
    MMControl1.Command = "Close"
 
You may want to hide the control in the form load event, or you may want to allow the user to control the play, it is up to you.  I hope this helps you out.

Later,
Martin

0
 

Author Comment

by:rzvika2
ID: 1960639
thank you mkmccreary or Martin.
i dont have the option of upgrade the points, so tell me what or where to ask you this question again so that i could give you what i prommised.
btw, i didn't try it but if i have questions i will ask you again. thank you very much.
0
 

Author Comment

by:rzvika2
ID: 1960640
when i insert the  Microsoft Music Control it says:
"the operation on the ole object failed
the ole server may not be registered"
what sould i do?
thank you again!
0
 
LVL 2

Expert Comment

by:mkmccreary
ID: 1960641
I try to insert the Music control and get the same error, but there is a good side, we don't want to use this control.  We want to use the Microsoft Multimedia Control.  

Hope It Helps,
Martin

0
 

Author Comment

by:rzvika2
ID: 1960642
i dont have Microsoft Multimedia Control (what is its exact name?) and how can i install it , if necesary?
and by the way, if i want to play a wave or avi, and i don't want a control like in the compact disc(play , forward etc...)do i have to put that activeX anyway?
0
 
LVL 2

Expert Comment

by:mkmccreary
ID: 1960643
Try this code and see if it works.  It is Dim-ing a control of type MMControl which I use to play the files.  I placed this code under a command button to test it.  You will also have to change the filenames to valid files.  Try this:

Dim MMControl1 As New MMControl

    ' Initialize the control
    MMControl1.Notify = False
    MMControl1.Wait = True
    MMControl1.Shareable = False
     
    ' Play a wave file
    MMControl1.DeviceType = "WaveAudio"
    MMControl1.FileName = "c:\WINNT\Media\chimes.WAV"
    MMControl1.Command = "Open"
    MMControl1.Wait = True
    MMControl1.Command = "Play"
    MMControl1.Command = "Close"
     
    ' Play an AVI file
    MMControl1.DeviceType = "AVIVideo"
    MMControl1.FileName = "c:\WINNT\clock.avi"
    MMControl1.Command = "Open"
    MMControl1.Wait = True
    MMControl1.Command = "Play"
    MMControl1.Command = "Close"

Set MMControl1 = Nothing

Just Drop Me A note and let me know how it works.

0
 

Author Comment

by:rzvika2
ID: 1960644
it doesn't know the MMcontrol .
i installed the whole office.
when i try to search in help about the MMcontrol, it doesn't find anything.
0
 
LVL 2

Expert Comment

by:mkmccreary
ID: 1960645
I figured out that the multimedia control does not ship with Office97, it ships with certain versions of VB as well as the Office Developers Addition.  I have found an article on how to do it with API calls.  The article is on Microsofts homepage at http://www.microsoft.com/accessdev/articles/movies.htm.  It covers both .AVI and .WAV files.  I didn't have time to read the whole article.

Good Luck,
Martin

0
 

Author Comment

by:rzvika2
ID: 1960646
hate to waste your time, thank you for keeping answering me!
now:
if i have the developer edition, it will be ok?
second, if i have the vb5 installed on my computer, this should work? should i do something special?(in the installation stage)?
thank you anyway.

0
 
LVL 2

Expert Comment

by:mkmccreary
ID: 1960647
It should be ok if you have the Developer's Addition or VB5 on your computer.  I didn't have to register the controls in Access, they were just there when I went to access them.  You may have to go to the Tools - ActiveX Controls menu option and select the register button to find and register the control.  Bye the way the name of the control is MCI32.OCX.

Hope this helps,
Martin

P/S  I'm not sure which versions of VB include the MCI32.OCX control.
0
 

Author Comment

by:rzvika2
ID: 1960648
mkmccreary:
TTTTTTAAAAAAAAANNNNNNNNNNKKKKKKKKKK you!!!
i went to : "http://www.microsoft.com/accessdev/articles/movies.htm"
and the actually put a word file with an access aplication that demonstrate what i wanted!!!
now tell me how can i give you more 40 pointes!
thank you again!!!
zvi.
0
 

Author Comment

by:rzvika2
ID: 1960649
can you help me with playing wav files in java applications?
it is quite urgent and i haven't got answer about this(the answers was about applets).

0
 
LVL 2

Expert Comment

by:mkmccreary
ID: 1960650
Sorry,
    No Java experience at this time.  I guess you could post a question and just put in the title for me only.  I don't know.  I just started playing last week.  I'm glad I was able to help you out.

Later,
Martin

0
 

Author Comment

by:rzvika2
ID: 1960651
mkmccreary, how can i ask you (and only you) a question ?
in any case, i asked a question about the details in a report.
if you could answer it, i will be very glad!
by
zvi.
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question