Hi all,
What I am trying to do is simple (in theory), but as I'm very much new to Access and VBA I can't seem to get it right. I have a simple add form that will add an entry to one of two tables: Box 1-1, or Box 1-2. They have the exact same fields. In the add form, I want the user to specify a Rack field and a Box field. Box 1-1 would be Rack 1, Box 1. Box 1-2 would be Rack 1, Box 2 (there will be more tables later, but just using two for now until I get it working).
But right now my form always adds to the table Box 1-1 - I can't get it to switch.
I've added the code I have so far below. The first part is what I'm having trouble with, the second part just uses a Submit button to add a new record - I'm just including it in case it's interfering in some way I'm not seeing.
Any help or advice on how I might better go about this would be greatly appreciated! I'm running Access 2007.
What I am trying to do is simple (in theory), but as I'm very much new to Access and VBA I can't seem to get it right. I have a simple add form that will add an entry to one of two tables: Box 1-1, or Box 1-2. They have the exact same fields. In the add form, I want the user to specify a Rack field and a Box field. Box 1-1 would be Rack 1, Box 1. Box 1-2 would be Rack 1, Box 2 (there will be more tables later, but just using two for now until I get it working).
But right now my form always adds to the table Box 1-1 - I can't get it to switch.
I've added the code I have so far below. The first part is what I'm having trouble with, the second part just uses a Submit button to add a new record - I'm just including it in case it's interfering in some way I'm not seeing.
Any help or advice on how I might better go about this would be greatly appreciated! I'm running Access 2007.
Code:
Private Sub Switch_BeforeUpdate()
If Me.Rack.Value = "1" And Me.Box.Value = "1" Then
Form_Add.RecordSource = "Box 1-1"
ElseIf Me.Rack.Value = "1" And Me.Box.Value = "2" Then
Form_Add.RecordSource = "Box 1-2"
End If
Form_Add.Requery
End Sub
Private Sub Submit_Click()
On Error GoTo Err_cmdSave_Click
If MsgBox("Are you sure you want to save?", vbOKCancel) = vbOK Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox ("Save successful.")
DoCmd.GoToRecord , , acNewRec
Exit_cmdSave_Click:
Exit Sub
Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click
End If
End Sub