Getting 2 records on one click

[adamstuff]

Hi Folks,
I have a form bound to Table1 and a subform in it bound to Table3. When I click on the SUBMIT button, I get 2 records added in Table1 and 1 record in Table3. Table3 data seems OK. In Table1 the first record created has all the data fields filled, but not the name, date and empolyeeID fields. The next record has the opposite. Name, date and employeeID are filled, but none of the data fields. The Autonumber Primary field update on each record, the the table does have 2 new records.
Being new to Access (2003) I'm lost. Here's the code:

Private Sub btnclmssbmt_Click()
On Error GoTo Err_btnclmssbmt_Click

'We need to paste new data for the subform (table 1)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Table1")
rs.AddNew
rs!EmpID = Forms!Main!EmpID
rs!Name = Forms!Main!UserName
rs.Update
rs.Close

'We need to paste new data for the subform (table 3)
Set rs = CurrentDb.OpenRecordset("Table3")
rs.AddNew
rs!EmpID = Forms!Main!EmpID
rs!Date = Forms!Main!Date
rs.Update
rs.Close
    
'Close the form
    DoCmd.Close
'Open the Employee Menu form
    DoCmd.OpenForm "Emp_Menu"

Exit_btnclmssbmt_Click:
    Exit Sub

Err_btnclmssbmt_Click:
    MsgBox Err.Description
    Resume Exit_btnclmssbmt_Click
    
End Sub

Sorry for being so windy.
Any ideas on what I'm doing wrong?
Any help would be appreciated.
TIA





Heisenberg was probably right.

 

[ZmrAbdulla]

Try like here..

Private Sub btnclmssbmt_Click()
On Error GoTo Err_btnclmssbmt_Click

'We need to paste new data for the subform (table 1)
Dim rs As DAO.Recordset
[b]Dim rs1 As DAO.Recordset[/b]

Set rs = CurrentDb.OpenRecordset("Table1")
rs.AddNew
rs!EmpID = Forms!Main!EmpID
rs!Name = Forms!Main!UserName
rs.Update
rs.Close

'We need to paste new data for the subform (table 3)
Set [b]rs1[/b] = CurrentDb.OpenRecordset("Table3")
[b]rs1[/b].AddNew
[b]rs1[/b]!EmpID = Forms!Main!EmpID
[b]rs1[/b]!Date = Forms!Main!Date
[b]rs1[/b].Update
[b]rs1[/b].Close
    
'Close the form
    DoCmd.Close
'Open the Employee Menu form
    DoCmd.OpenForm "Emp_Menu"

Exit_btnclmssbmt_Click:
    Exit Sub

Err_btnclmssbmt_Click:
    MsgBox Err.Description
    Resume Exit_btnclmssbmt_Click
    
End Sub

________________________________________________________
Zameer Abdulla
Help to find Missing people
Even a thief takes ten years to learn his trade.

 

[adamstuff]

Thanks ZmrAbdullah,
I will give it a try. I also thought of a reason just as I went to bed last night. The very items, employeeID, Date, and Name are gotten from Table2. Those fields are entered when a user logs into the switchboard. I'm thinking that creates the first record in Table3. And then the form creates the second record. I don't see anything in the switchboard that would cause it to write, though.
Here's the code for the switchboard submit button.

Private Sub btnmainsubmit_Click()

' DoCmd.OpenTable "T", acViewPreview

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim Criteria As String, UsrNam As String, UsrNum As String

On Error GoTo Err_btnmainsubmit_Click

Set dbs = CurrentDb

' rst is RecordSet
Set rst = dbs.OpenRecordset("Table2", dbOpenDynaset)
UsrNam = Me!UserName
UsrNum = Me!EmpID
Criteria = "Left([First Name], 1) = '" & Left(UsrNam, 1) & "' AND " & _
    "[Last Name] = '" & Right(UsrNam, Len(UsrNam) - 1) & "' AND " & _
    "Right([EmpID], 6) = '" & UsrNum & "'"
    
rst.FindFirst Criteria

If rst.NoMatch Then
    MsgBox "User Not Found!"
Else
    Select Case rst!Form
        Case 1
            DoCmd.OpenForm "Emp_Menu"
        Case 2
            DoCmd.OpenForm "Mgr Menu"
        Case Else
            DoCmd.OpenForm "Form 3"
    End Select
End If

Set rst = Nothing
Set db = Nothing

' DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70

Exit_btnmainsubmit_Click:
    Exit Sub

Err_btnmainsubmit_Click:
    MsgBox Err.Description
    Resume Exit_btnmainsubmit_Click

The form has the 3 fields data source as such: =Forms!Main!UserName where Main is the switchboard and and UserName is the employeeID. The proper forms are opened when the user types in their User Name and Password and hits the SUBMIT button. Was this the wrong way to populate the 3 fields and is there a better/right way?

TIA

Heisenberg was probably right.

 

[ZmrAbdulla]

Here in the Switchboard code you are not Adding or editing any record. You only opend and find the user with the criteria. This will not make any new record.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Even a thief takes ten years to learn his trade.

 

[adamstuff]

Thanks for the input, ZmrAbdullah,
I did try the changes that you suggested. It didn't make a difference. Thanks for looking over the code, it's appreciated.
I've ben trying to watch the tables as I input test data. So far I haven't been able to determine anything. I just realized that the tables don't get updated while I have them open, so now I'm going to try to close and open them at each step to see if there is a specific time the data is written and if it is separately or at the same time.
I'm also going to try to only write to one table from the SUBMIT button to see what that changes. Hopefully I'll have more information to work with so I can solve this problem.
Thanks again for all your help.


Heisenberg was probably right.

 

[adamstuff]

OK,
Didn't think this was important, but apparently it is. There is a subform on my form. What I've found is that just clicking on the subform writes a record in Table1 and Table3. Then clicking on SUBMIT on the form creates another record in Table1 with just name, date and employeeID. I haven't been able to determine whether it's the form's exit or the subform's entry that does it. There are no Event calls on the subform.
Does this ring any bells for anyone? is this normal behavior for form/subform?

TIA

Heisenberg was probably right.

 

[BoxHead]

adamstuff,

It seems that if you're entering data into a form bound to Table1, you are creating the new record as you enter the data. The code is then adding that data again.

Try commenting out the portion of your code where you add new to Table1 or try using unbound textboxes on the form.

HTH

 

[adamstuff]

BoxHead,
I bound the form, and commented out the AddNew to Table section of code. Same result. Leaves out EmployeeID and Name. These are bound by: =Forms!Main!UserName where Main is the switchboard and and UserName is the employeeID.

The really annoying thing is that almost the exact same code works(!) for Table3.

Heisenberg was probably right.

 

[adamstuff]

Finally got it figured out!
1. I don't need to do an rs.addnew for Table1. Access automatically adds a record and updates when leaving a form, the rs.addnew is redundant, and each field has to be specified to be added. Ugh. The whole segment of Table1 is deleted. I tried rs.edit, but it wouldn't do anything???
2. I changed the 3 textboxes to be bound to Table1 in the fields I needed, and in the Default Values I put the Forms!Main!xxxxx. This allows me to display the proper values and get them into the one record.

Thank you all for your suggestions and help. As always, I appreciate it.

Heisenberg was probably right.