use recordset to get data from a table and populate a form

[infoscion]

Hello:
I am writing VBA code to populate the controls on a form including list boxes, text boxes through the execution of code. However, I am running into a few issues.
I have a search page and once the user enters the ID, a form needs to be loaded that immediately opens the underlying table, check if the ID exss and if so then populates a form. Any ideas, suggestions, tips or code would be helpful.
Regards,
Info

 

[Frink]

Hallo,

Can't you just use a subform bound on a table and linked to the Id box?
Any subform List boxes can be populated on the OnCurrent event of the subform.

Not sure why you want to use code, rather than standard bound forms/controls.

- Frink

 

[infoscion]

Hello:
I have the following code
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
'open the special procedures from
Dim cont As String



DoCmd.OpenForm "Procedures_Tracking"
'hide the search form
Search_Special_Procedures_Tracking.RecordSource = "Tracking_Table"
Form.Item("Search_Record").Visible = False

'create the new instance for the record set
Dim rs As ADODB.Recordset


Dim f As Form


Set f = ThisForm.Form
'(======Question what form is it going to refer to?======)

Dim strSQL As String

Dim Cn As ADODB.Connection
Dim CmdCommand As New ADODB.Command
'Dim strCon As String
Dim strMSg As String, StrTitle As String

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Performance Engineering\Projects\abc"
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=" & Me.Patient_ID & ";"
MsgBox (strSQL)
rs.Open strSQL, Cn, adOpenDynamic, adLockOptimistic
If (rs.EOF) Then
'Query returned empty. Quit and print the error message:
MsgBox ("Error: Cannot find The ID in the the Table so exiting....")
Else
With f
If Not rs.EOF And Not rs.EOF Then
Do While Not rs.EOF
.ID = rs.Fields("ID").Value
.Text91 = rs.Fields("Date").Value
.List48 = rs.Fields("Type").Value
.Text33 = rs.Fields("RoomNo").Value
.Combo166 = rs.Fields("Name").Value

Loop
End If
End With
rs.Close

End If

Exit_Command5_Click:
Exit Sub

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


The error messages that pops up is "object required". Any ideas and suggestions would be helpful. I cannot link a table to a sub form becasue I need the information pertaining to the ID to be displayed in the form.
Any ideas would be appreciated.
Regards,
Info

 

[Frink]

Hallo,

I'm still a bit confused as to why you want to do this in code.
To display information pertaining to an ID to be displayed in a form you would generally use a bound form.
Do you understand how bound forms work and how they are used?
I'm guessing that because you have a button called Command5, that you are quite new to programming in MS Access?

- Frink

 

[infoscion]

Frink:
I guess you are right. It would be helpful incase you could guide as to how to tie in the bound form to the table. But do you think it will work becasue I need multiple users to access the data base at the same time.
Your inputs are apprecaited.
Regards,
Info

 

[Frink]

Hallo,

You can either have separate search and data display forms, or have them both on the same form (where actually the data is displayed on a subform)

If you can let me know which you want as they are both quite different. I prefer using the mainform/subform approach as I don't like popups, but that's a personal thing and would depend on the design of the application.
Is this a small part of a larger application, or is this it?


- Frink

 

[infoscion]

Frink:
Thank you for the feedback. This ia actually a small part of a moderately sized application. Any inputs would be greatly appreciated.
Regards,
Info

 

[Frink]

Hallo,

You can either have separate search and data display forms, or have them both on the same form (where actually the data is displayed on a subform)

If you can let me know which you want as they are both quite different. I prefer using the mainform/subform approach as I don't like popups, but that's a personal thing and would depend on the design of the application.

- Frink

 

[infoscion]

Hi Frink:
Thank you for the inputs. I got it to work with the inputs of another expert using the recordsets. Now I am having a syntax error with a big long Update query that I have built.
Again, thank you for your help and very insightful comments.
Regards,
Info