Save Current Record; Go To Next Record - two tables in form

[joave]

Hi all:

I have a form that is using two tables. Both tables house the exact same
columns, but one table is for one set of people, and the other table is for
another set of people. Data is entered on the form for both tables (in a
comparison sort of way) and I want to save the data to both tables through
the form at the same time with a command button. However, this is not working
no matter what I try.

I believe the problem may stem from the fact that the tables are connected
on the record number in a one to one relationship.

Here is the VBA code from what I am doing - I would highly appreciate any
suggestions to get this going

Private Sub ScoreCommand_Click()

Static Greets As Integer
Static Acknowledges As Integer
Static Verifies As Integer
Static Authenticates As Integer
Static Demonstrates As Integer
Static Builds As Integer
Static Addresses As Integer
Static Uses As Integer
Static Utilizes As Integer
Static Probes As Integer
Static Follows As Integer
Static Resolves As Integer
Static Creates As Integer
Static Provides As Integer
Static Takes As Integer
Static Accurately As Integer
Static Thanks As Integer
Dim Total(2) As Integer
Dim SubScore1 As Single
Dim VendorScore1 As Single
Dim temp1 As Single
Dim temp2 As Double
Total(1) = 100
Total(2) = 100
Greets = 2
Acknowledges = 3
Verifies = 3
Authenticates = 0
Demonstrates = 10
Builds = 10
Addresses = 1
Uses = 10
Utilizes = 6
Probes = 10
Follows = 10
Resolves = 7
Creates = 4
Provides = 7
Takes = 8
Accurately = 7
Thanks = 2

If IsNull(Text89.Value) Or Trim(Text89.Value) = "" Then
Dim n As Integer
n = MsgBox("Please place agent's initials in text box.", vbOKOnly)
Exit Sub
End If
If SubGreetsCombo.Value = "NA" Then Total(1) = Total(1) - Greets
If SubAcknowledgesCombo.Value = "NA" Then Total(1) = Total(1) - Acknowledges
If SubVerifiesCombo.Value = "NA" Then Total(1) = Total(1) - Verifies
If SubDemonstratesCombo.Value = "NA" Then Total(1) = Total(1) - Demonstrates


If VendorGreetsCombo.Value = "NA" Then Total(2) = Total(2) - Greets
If VendorAcknowledgesCombo.Value = "NA" Then Total(2) = Total(2) -
Acknowledges
If VendorVerifiesCombo.Value = "NA" Then Total(2) = Total(2) - Verifies
If VendorDemonstratesCombo.Value = "NA" Then Total(2) = Total(2) -
Demonstrates

SubScore1 = Total(1)
VendorScore1 = Total(2)

If SubGreetsCombo.Value = "No" Then SubScore1 = SubScore1 - Greets
If SubAcknowledgesCombo.Value = "No" Then SubScore1 = SubScore1 - Acknowledges
If SubVerifiesCombo.Value = "No" Then SubScore1 = SubScore1 - Verifies
If SubDemonstratesCombo.Value = "No" Then SubScore1 = SubScore1 - Demonstrates


If VendorGreetsCombo.Value = "No" Then VendorScore1 = VendorScore1 - Greets
If VendorAcknowledgesCombo.Value = "No" Then VendorScore1 = VendorScore1 -
Acknowledges
If VendorVerifiesCombo.Value = "No" Then VendorScore1 = VendorScore1 -
Verifies
If VendorDemonstratesCombo.Value = "No" Then VendorScore1 = VendorScore1 -
Demonstrates

temp1 = Format(SubScore1 / Total(1) * 100, "###0.00")
temp2 = Format(VendorScore1 / Total(2) * 100, "###0.00")
SubScore.Value = temp1
VendorScore.Value = temp2
If SubAuthenticatesCombo.Value = "No" Then SubScore.Value = 0
If VendorAuthenticatesCombo.Value = "No" Then VendorScore.Value = 0
Dim y As Integer
y = MsgBox("Click Yes to advance to next record, No to rescore this
record)", vbYesNo)
If y = 7 Then Exit Sub
SendKeys "%egw"

'RunCommand acCmdSaveRecord
'If Me.Dirty Then Me.Dirty = False
'DoCmd.GoToRecord , , Me.CurrentRecord + 1
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
ReloadMe


End Sub

Private Sub ReloadMe()

SubAuthenticatesCombo.Value = "NA"
SubGreetsCombo.Value = "NA"
SubAcknowledgesCombo.Value = "NA"
SubVerifiesCombo.Value = "NA"
SubDemonstratesCombo.Value = "NA"

VendorAuthenticatesCombo.Value = "NA"
VendorGreetsCombo.Value = "NA"
VendorAcknowledgesCombo.Value = "NA"
VendorVerifiesCombo.Value = "NA"
VendorDemonstratesCombo.Value = "NA"
Text89.Value = ""
SubMonitorTable_ID.Value = ""
VendorMonitorTable_ID.Value = ""
SubScore.Value = ""
VendorScore.Value = ""
'SubMonitorTable_Call.Value = SubMonitorTable_Call.Value + 1
'VendorMonitorTable_Call.Value = VendorMonitorTable_Call.Value + 1

End Sub

Private Sub Text89_Exit(Cancel As Integer)


'Dim Greets As Integer: Greets = 2
SubMonitorTable_ID.Value = Text89.Text
VendorMonitorTable_ID.Value = Text89.Text
SubMonitorTable_Date.Value = Text91.Value
VendorMonitorTable_Date.Value = Text91.Value
' VendorMonitorTable_Date.Value = Greets
End Sub

 

[joave]

I failed to mention that the form has text boxes and combo boxes on it that
are bound to the tables of record. So the Sub combo boxes are bound to the
fields from the Sub table and the Vendor boxes are bound to the Vendor table.

Thank you.