fill in a form from a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My table and form have same field names and textbox control names, every
field in table has a textbox. I have the user select the key to the table
record they want. How do I use the table data to fill in the form textboxes?
 
Arnold said:
My table and form have same field names and textbox control names,
every field in table has a textbox. I have the user select the key to
the table record they want. How do I use the table data to fill in
the form textboxes?
Click to expand...

(untested air code)

Dim db as Database
Dim rs As Recordset
Dim fld as Field
Dim sql as String

sql = "SELECT * FROM TableName WHERE KeyField = SomeValue"
Set db = CurrentDB
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

For Each fld in rs!Fields
Me(fld.Name) = fld
Next fld

rs.Close
Set rs = Nothing
Set db = Nothing
 
Here is my code from your sample:

Public Sub FillForm(intID As Integer)
Dim db As Database
Dim rs As Recordset
Dim fld As Field
Dim sql As String

sql = "SELECT * FROM [tblCase_Associated Individuals] WHERE
caiAssociatedIndividualID ="
sql = sql & intID
Debug.Print sql ' this runs fine
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

For Each fld In rs!Fields ***error here (see below)
Me(fld.Name) = fld
Next fld

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

** I am getting a "Run time error 3265 item not found in this collection"

This is the code that calls it

Private Sub cboIndividualFirst_AfterUpdate()
Dim intID As Integer
intID = cboIndividualFirst.Column(3)
FillForm (intID)
Me!cboIndividualFirst = Null
End Sub

Have any ideas?
 
Arnold said:
Here is my code from your sample:

Public Sub FillForm(intID As Integer)
Dim db As Database
Dim rs As Recordset
Dim fld As Field
Dim sql As String

sql = "SELECT * FROM [tblCase_Associated Individuals] WHERE
caiAssociatedIndividualID ="
sql = sql & intID
Debug.Print sql ' this runs fine
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

For Each fld In rs!Fields ***error here (see below)
Click to expand...
[snip]

Sorry, try rs.Fields instead of rs!Fields.
 
Back
Top