Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    16

    Question Unanswered: findrecord in another table using value of textbox

    I have a form/table which I want to use the value of a textbox to find a record in another table and then edit that record (the value is an autonumber in that other table). Have tried a variety of ways. This current code gives me run time error 2162 on the findrecord line.

    Dim UCID As Variant
    UCID = Forms![Council Workflow Form].[Unique Council ID].Value
    Forms![Council Workflow Form].[Unique Council ID].SetFocus

    Dim rsToCouncilTable As Object 'to send response to council table
    Set rsToCouncilTable = CurrentDb.OpenRecordset("Council Table")
    rsToCouncilTable.Edit
    DoCmd.FindRecord UCID, , , , , , True
    rsToCouncilTable![Unique order id] = [Unique order id]
    rsToCouncilTable![Unique Patient ID] = [Unique Patient ID]
    rsToCouncilTable.Fields(Forms![Council Workflow Form].[Linked Control]) = [Resp Answer]
    rsToCouncilTable.Update
    rsToCouncilTable.Close

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Few of us have error codes memorized. Please tell us what a 2162 is.

    Besides, why do you set the UCID value before setting the focus? Also, why would you open the recordset for .edit before finding the correct record? Finally, since the value is an autonumber, which is a data type long, maybe you should change the Dim of UCID from variant to long to be compatible with the target data.

    Sam
    Last edited by Sam Landy; 12-19-12 at 22:11.

  3. #3
    Join Date
    Oct 2011
    Posts
    16
    Sorry about that, it is:

    Run-time error '2162':
    A macro set to one of the current field's properties failed because of an error in a FindRecord argument.

    I'm a bit green, I end up trying to use what previous co-workers have done as a reference.. but .. i did make the changes you suggested .. still receive the same error, on the same line.

    Dim UCID As Long
    Forms![Council Workflow Form].[Unique Council ID].SetFocus
    UCID = Forms![Council Workflow Form].[Unique Council ID].Value

    Dim rsToCouncilTable As Object 'to send response to council table
    Set rsToCouncilTable = CurrentDb.OpenRecordset("Council Table")
    DoCmd.FindRecord UCID, , , , , , True
    rsToCouncilTable.Edit
    rsToCouncilTable![Unique order id] = [Unique order id]
    rsToCouncilTable![Unique Patient ID] = [Unique Patient ID]
    rsToCouncilTable.Fields(Forms![Council Workflow Form].[Linked Control]) = [Resp Answer]
    rsToCouncilTable.Update
    rsToCouncilTable.Close

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    One of two things is wrong. Either UCID is not getting initialized correctly, or you're using the .FindRecord method incorrectly. It's probably the latter; however to rule out the former, run your program step-by-step and check the value of UCID after the line in which it's initialized, but before the .FindRecord line. If it's incorrect, that's your problem. Like I said, I doubt it's the problem.

    If you read the fine print, .FindRecord looks for data in the active table. Opening a record using the .OpenRecordset method doesn't make it the active table. The active table is still the form's Record Source, which is a different table.

    What you can do is to open Council Table in a different form that has Council Table as the Record Source, and .SetFocus to the correct field. You can now use
    Code:
    DoCmd.FindRecord Forms![Council Workflow Form].[Unique Council ID], , , , , , True
    which should find the correct record. You can then do your edits in VBA. There are several ways to do this correctly.

    Sam

  5. #5
    Join Date
    Oct 2011
    Posts
    16
    Hey Sam,
    So after more research and lots of trial and error, i got this to do exactly what i wanted:

    Dim UCID
    Forms![Council Workflow Form].[Unique Council ID].SetFocus
    UCID = Forms![Council Workflow Form].[Unique Council ID].Value

    Dim dbCouncil As DAO.Database
    Dim rsToCouncilTable As DAO.Recordset
    Set dbCouncil = CurrentDb()
    Set rsToCouncilTable = dbCouncil.OpenRecordset("SELECT * FROM [Council Table] WHERE [Unique Council ID] = " & UCID)
    With rsToCouncilTable
    .Edit
    .Fields("Unique order id") = Me.Unique_order_id
    .Fields("Unique Patient ID") = Me.Unique_Patient_ID
    .Fields(Forms![Council Workflow Form].[Linked Control]) = Me.Resp_Answer
    .Update
    End With


    Thank you for your help.

Tags for this Thread

Posting Permissions

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