Filling form controls from tables (2003 SP-3)

Topic

New Reply

I have a form and two tables. The first table contains the form control name and field name from the second table. The second table contains several thousand records. The user selects the record they want using a combobox on the form. After selecting the record, the “After Update” event it runs the following code to populate individual controls on the form. I get error 2465 Microsoft Office Access can’t find the field ‘EmpName’ referred to in your expression on the
“If Me.Controls(rst!FieldName).Name = rstData.Fields(k).Name Then” line

Private Sub cboCaseNum_AfterUpdate()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstData As DAO.Recordset
Dim strSQL As String
Dim PK As Integer
Dim k As Integer

PK = Me.cboCaseNum

strSQL = “SELECT ControlName, FieldName ” & _
“FROM FormFields ” & _
“WHERE UseForForm = True ” & _
“ORDER BY FormFields.FieldName;”

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

strSQL = “SELECT [txtLastName] & ‘, ‘ & [txtFirstName] AS EmpName, [curDamage]+[curOtherCost] ” & _
“AS ExpPaid, curDemand, datDOB, datIncident, txtCaseNum, Company, txtJob, txtLocation, ” & _
“FROM tblInc INNER JOIN tblIncIns ON tblInc.txtCaseNum = tblIncIns.fkCaseNum ” & _
“WHERE pk_Inc= ” & PK & “;”

Set rstData = db.OpenRecordset(strSQL, dbOpenSnapshot)
Do Until rst.EOF = True
For k = 0 To rstData.Fields.Count – 1
If IsNull(rstData.Fields(k).Value) = False Then
If Me.Controls(rst!FieldName).Name = rstData.Fields(k).Name Then
Me.Controls(rst!ControlName) = rstData.Fields(k).Value
k = rstData.Fields.Count – 1
End If
End If
Next k
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
rstData.Close
Set rstData = Nothing
Set db = Nothing

Any ideas? Thanks in advance for any ideas.

Ken

Reply | Quote

Replies

I am not sure but….

You say that :”The first table contains the form control name and field name from the second table.”, yet what you are trying to display is a query that includes calculated fields – empName is one such calculated field.
Is there anything in your setup to say where these calculated fields are supposed to go on the form?

Reply | Quote

John,

Thanks for following up. The first table (and the rst recordset) contains the name of the control on the form (e.g. txtName) and the name of the field in the query (e.g. EmpName). The second table contains hundreds of records and the second recordset (rstData) queries the table for a single record. The fields in rstData correspond the the fieldName column in rst.

Columns below are sample from the first table (and the rst recordset)

ControlName FieldName
__________ ________
txtName EmpName
txtExpensePaid ExpensePaid
txtSettlementDemand curDemand

EmpName ExpensePaid curDemand
________ ___________ __________
Finney, Jacob 150.00 3,000.00

Does that help?

Thanks for your help.

Ken

Reply | Quote

John,

I went back and reviewed my code and discovered the problem in code.
changed
If Me.Controls(rst!FieldName).Name = rstData.Fields(k).Name Then
to
If rst!FieldName = rstData.Fields(k).Name Then

and it worked.

THANKS!

Ken

Reply | Quote