Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    13

    Unanswered: Go from Multiple Items Form to Same Record in Single-Record Form

    Hello dbForums.

    I have a database full of machines, each of which has a unique serial number (the primary key). Some of these serial numbers contain alphabet characters, others are all numerals, but the data type of this field is text.

    I have a multiple items form that shows all the machines in a datasheet-type format. I do not have all the fields included in this multiple items form because if I did, the form would be way too wide (especially with the "Notes" field). I have a single-item form with all the fields displayed in stacks.

    What I want to do is have a button in the form header of the multiple items form that executes a command that will open the single-item form and go to the same record that the focus was on the multiple items form.

    My first instinct was to try something like this:
    Dim vCurrentSerial As Variant
    vCurrentSerial = [Serial].Value
    DoCmd.OpenForm "frmSingleMachine", , , "[Serial] = " & vCurrentSerial

    With the above code, one of 4 results occur depending on the serial number (returned serial numbers vary):
    1. Serial number contains 2 alphabet characters
    Run-time error '3075'
    Syntax error (missing operator) in query expression '[Serial] = 50GE07439'.

    2. Serial number contains 1 alphabet character
    Run-time error '3075'
    Syntax error in number in query expression '[Serial] = 02E0100107

    3. Serial number contains no alphabet characters
    Run-time error '3464'
    Data type mismatch in criteria expression

    4. Serial number contains 3 or more alphabet characters
    Enter Parameter Value
    CNGXH00375
    ___________

    I tried this code:
    Private Sub Command43_Click()
    DoCmd.OpenForm "frmViewMachine", , , "[Serial] = " & Me.Serial
    End Sub
    with the same results.

    Is there another set of code that will work to do the above task?

    I don't want my users to have to copy the serial number, then go to the other form, then Ctrl+F paste it in the Find field. Click "Find Next" and then close the Find dialog box. Now that I mention it, is there vba code that can do it this way?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    DoCmd.OpenForm "frmViewMachine", , , "[Serial] = '" & Me.Serial.Value & "'"
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    13
    Thank you so much, Sinndho! It works perfectly now.

    Now I wonder why that apostrophe business wasn't on other websites and forums.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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