Code:
Private Sub Command17_Click()
Dim db As Database
Dim rs As Recordset
Dim DocNr, TransType, Site, TransDate, HireStart, Comments As String
'I am naming going to do the code in three parts for the three column in the form
Dim a, b, c As Long
'Column 1
Dim cmb1, cmb2, cmb3, cmb4, cmb5, cmb6, cmb7, cmb8, cmb9, cmb10, cmb11 As String
Dim txt1, txt2, txt3, txt4, txt5, txt6, txt7, txt8, txt9, txt10, txt11 As String
Set db = CurrentDb()
'this opens up the database the form is in. Leave as is.
Set rs = db.OpenRecordset("tbl_transactionlist")
'and this opens up the recordset you want to add the records to. It is the name of the table in "s
DocNr = Nz(Me.txt_docnr, "")
TransType = Nz(Me.cmb_transtype, "")
Site = Nz(Me.cmb_site, "")
TransDate = Nz(Me.txt_transdate, "")
HireStart = Nz(Me.txt_hirestart, "")
Comments = Nz(Me.txt_comments, "")
'Main If to check if document information is there
If DocNr = "" Then
MsgBox ("You have not completed the Document Number. Please include this and retry.")
Exit Sub
Else
End If
If TransType = "" Then
MsgBox ("You have not selected a transaction type. Please include this and retry.")
Exit Sub
Else
End If
If Site = "" Then
MsgBox ("You have not chosen a site for the transaction. Please include this and retry.")
Exit Sub
Else
End If
If TransDate = "" Then
MsgBox ("You have not selected a transaction date. Please include this and retry.")
Exit Sub
Else
End If
If HireStart = "" Then
MsgBox ("You have not selected a hire start date. Please include this and retry.")
Exit Sub
Else
End If
'this is for the main document information
With rs
'This implies that there is rs before each . or !
.AddNew
!Doc_nr = DocNr
!Trans_Type = TransType
!Site = Site
!Date_Of_Transaction = TransDate
!Hire_Start_Date = HireStart
!Comments = Comments
.Update
.Close
End With
'this will save the first column to the table
Set rs = db.OpenRecordset("tbl_transactions")
'and this opens up the recordset you want to add the records to. It is the name of the table in "s
a = 1
cmb1 = Nz(Me.Combo1, "")
cmb2 = Nz(Me.Combo2, "")
cmb3 = Nz(Me.Combo3, "")
cmb4 = Nz(Me.Combo4, "")
cmb5 = Nz(Me.Combo5, "")
cmb6 = Nz(Me.Combo6, "")
cmb7 = Nz(Me.Combo7, "")
cmb8 = Nz(Me.Combo8, "")
cmb9 = Nz(Me.Combo9, "")
cmb10 = Nz(Me.Combo10, "")
cmd11 = Nz(Me.Combo11, "")
txt1 = Nz(Me.Text1, "")
txt2 = Nz(Me.Text2, "")
txt3 = Nz(Me.Text3, "")
txt4 = Nz(Me.Text4, "")
txt5 = Nz(Me.Text5, "")
txt6 = Nz(Me.Text6, "")
txt7 = Nz(Me.Text7, "")
txt8 = Nz(Me.Text8, "")
txt9 = Nz(Me.Text9, "")
txt10 = Nz(Me.Text10, "")
txt11 = Nz(Me.Text11, "")
With rs
For c = 1 To 11
If "cmb" & (a) <> "" Then
If "txt" & (a) <> "" Then
.AddNew
!Doc_nr = DocNr
!Item_Code = "cmb" & (a)
!Quantity = "txt" & (a)
.Update
Else
End If
Else
End If
a = a + 1
Next c
.Close
End With
MsgBox "This transaction has been saved.", vbOKOnly
DoCmd.Close acForm, "frm_capture_new_transaction", acSaveNo
Set db = Nothing
Set rs = Nothing
'These close the recordset
End Sub