Error with command button which opens form to specific record

[extrym]

I have 2 forms, "Staff" and "Security" (without the ""'s). The Staff form has the following fields:
staff_ID
staff_name
staff_number
staff_DOB
priority

The Security form contains the following fields:
staff_ID
staff_pass
priority

The staff_ID fields in both forms are linked by a one-to-one relationship.
I have a command button (Command22) in the Staff form which, when pressed, opens the security form to the corresponding staff_ID value (for example, if I navigate to the record in the Staff form where staff_ID = 6 and press the Command22 button, the Security form is opened and the record in the Security form where staff_ID = 6 is displayed). Below is the code for the On Click event of the Command22 button:

Private Sub Command22_Click()
On Error GoTo Command22_Click_Err

    DoCmd.OpenForm "Security", acNormal, "", "[staff_ID]=" & staff_ID, , acNormal


Command22_Click_Exit:
    Exit Sub

Command22_Click_Err:
    MsgBox Error$
    Resume Command22_Click_Exit

End Sub

However, when I create a new record in the Staff form and press Command22, a "Syntax error (missing operation) in query expression '[staff_ID]='" error comes up. I think this is because the database cannot find a corresponding staff_ID value in the Security form. What needs to be done so that, when a new record is created in the Staff form and the Command22 button is pressed, the Security form is opened to a new record?

Thanks in advance,
extrym

[gnawoncents]

Hello, extrym.

This should be a fairly easy problem to resolve, but we need a little extra data. When you say,

The staff_ID fields in both forms are linked by a one-to-one relationship.

do you mean that you have two distinct tables linked in a query by a one-to-one relationship? If so, are the forms using that query as their Record Source or the tables directly?

If they are separate tables, how does the staff_ID get into each of the tables initially?

Please explain a bit more and we'll be glad to help.

[ADezii]

I have 2 forms, "Staff" and "Security" (without the ""'s). The Staff form has the following fields:
staff_ID
staff_name
staff_number
staff_DOB
priority

The Security form contains the following fields:
staff_ID
staff_pass
priority

The staff_ID fields in both forms are linked by a one-to-one relationship.
I have a command button (Command22) in the Staff form which, when pressed, opens the security form to the corresponding staff_ID value (for example, if I navigate to the record in the Staff form where staff_ID = 6 and press the Command22 button, the Security form is opened and the record in the Security form where staff_ID = 6 is displayed). Below is the code for the On Click event of the Command22 button:

Private Sub Command22_Click()
On Error GoTo Command22_Click_Err

    DoCmd.OpenForm "Security", acNormal, "", "[staff_ID]=" & staff_ID, , acNormal


Command22_Click_Exit:
    Exit Sub

Command22_Click_Err:
    MsgBox Error$
    Resume Command22_Click_Exit

End Sub

However, when I create a new record in the Staff form and press Command22, a "Syntax error (missing operation) in query expression '[staff_ID]='" error comes up. I think this is because the database cannot find a corresponding staff_ID value in the Security form. What needs to be done so that, when a new record is created in the Staff form and the Command22 button is pressed, the Security form is opened to a new record?

Thanks in advance,
extrym

'Force a Record Save
If Me.Dirty = True Then Me.Dirty = False

'No Security Record for Staff ID
If DCount("*", "tblSecurity", "[staff_ID]=" & Me![staff_ID]) = 0 Then
  DoCmd.OpenForm "Security", acNormal, , , acFormAdd
Else
  DoCmd.OpenForm "Security", acNormal, "", "[staff_ID]=" & staff_ID, , acNormal
End If

[extrym]

@ADezii
the same error appears again...

@gnawoncents
yes, they're two seperate tables. The staff_ID is actually a field in the Staff table. The staff_ID field in the Security table is just a lookup of the original staff_ID.

* edit *

Okay, I've solved it! I used a few macros to get the job done:
Macro name
Macro6

• OnError
  • Macro Name. Macro8
• RunCommand
  • SaveRecord
• OpenForm
  • Security، Form، ، ="[staff_ID]=" & [staff_ID]، ، Normal

Macro8

• RunCommand
  • SaveRecord
• OpenForm
  • Security. Form . . . Normal
• GoToRecord
  • Form. Security. New

When I converted it to VB code it didn't work...so I stuck to macros. If this method is wrong, please post.