Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Filling form controls from tables (2003 SP-3)

    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

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Filling form controls from tables (2003 SP-3)

    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?
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Filling form controls from tables (2003 SP-3)

    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

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Filling form controls from tables (2003 SP-3)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •