object required error

[infoscion]

Hi All:
I have been working with access and I am trying to import fields onto a form from table depending upon a search criteria. I do not hve a lot of expereince with VBA.

I am getting the error "object required". Please tell me as to why I am getting this error.
Regards,
Info

 

[PHV]

Any chance you could post your code and the highlihted line ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[infoscion]

Hi PHV:
Thank you for your response. Actially I am populating a from through a query. I have written this code. My VBA is not very good. But when this code is executed I get this error "object required".

Through this code, I want the program to do the following:
Once the ID is entered on the "Search _Record _2" form the and the command button named Search is clicked the SQL statement pulls the information from table and depending upon any match it populates the correspondning controls on the form ("Serach_Record_2") that includes list boxes, combo boxes and text boxes.

Your inputs are appreciated.
Thanks in advance for your consideration.
Info
* PS-I do not know how to highlight the line of the code.


Private Sub Search_Click()
On Error GoTo Err_Command5_Click

Dim f As Form


Set f = Currentform.Form

Dim rs as ADODB.Recordset
set cn=new ADODB.Connection

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\special"
Cn.Open strCon
'open the recordset

Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
strSQL = "SELECT ID FROM Tracking_Table WHERE ID=" & Forms.Search_Record_2.Controls!ID & ";"
MsgBox (strSQL)
rs.Open strSQL, Cn, adOpenDynamic, adLockOptimistic
If (rs.EOF) Then
'Query returened empty. Quit and print the error message:
MsgBox ("Error: Cannot find The Ptient ID in the the Special procedures Tracking Table so exiting....")
Else
With f
If Not rs.EOF And Not rs.BOF Then
Do While Not rs.EOF
.ID = rs.Fields("ID").Value
.Text12 = rs.Fields("Date").Value
.List14 = rs.Fields("Typeprocedure").Value
.Text16 = rs.Fields("RoomNo").Value
.List18 = rs.Fields("Name").Value
.Combo20 = rs.Fields("Time_for").Value

Loop
End If
End With
rs.Close

End If
MsgBox (strSQL)





Cn.Close
Set Cn = Nothing


Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
Exit Sub
End Sub

 

[PHV]

I do not know how to highlight the line of the code
By choosing Debug in the error messagebox.
My guess, replace .ID, .Text12 .... by !ID, !Text12 ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[infoscion]

Hi PHV:
Thnaks PHV and as per your suggestion I did make the changes i.e. replaced the '.' with '!' but it does not seem to work. I was wondering as to whether there is a way to set a current form as something like the currentDB thing. Please let me know. I know I am messing it up and I have no clue how to fix it.
Thanks PHV for your inputs

Also there is no error message on the VBA code but the error pops up on the form. I do not know as to whether this helps you or not but your inputs are greatly welcome.
Regards,
Info

 

[PHV]

BTW I didn't realize...
Replace this:
Set f = Currentform.Form
By this:
Set f = Screen.ActiveForm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[infoscion]

Hi PHV:
Thank you very much for the input. Many, many thanks to you. Here is another thing that is happening. The databse is hanging up. Am not sure why. Your ideas are solicited.
Regards,
Info

 

[PHV]

You are stuck on the first record as you have no call to MoveNext inside your loop.
Anyway, why looping ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[infoscion]

Hi PhV:
Thank you very much. Now it works. You are a genius!!!
One more twist, can I have this recordset read into a sub form OR transfer the contents of the recorset into another from. Please advise.If so how do i get to it?
Again, thanks PHV.
Regards,
Info