Hi, I've been working on this database for someone for some time now and I have run stuck again. I do not totally understand how to get the multiple choice selections from a list box to a table. I've been able to get the other information to write but run stuck here.
I also want to find a way to check that a check box has been selected and run an error if both are unchecked.
Below is what I have so far. It's all being done at the save_click and writing to a table that has a column for each list box. There are a total of about 20 different list boxes but I'm just starting with a few to make sure I can get it first. The first one starts at column 7 on the table. Here's what I have so far....
Private Sub Save_Bid_Click()
Dim rs As New ADODB.Recordset
Dim varItem As Variant
On Error GoTo ErrorHandler
'Check to see if the location field is not blank
If IsNull(Me.txtLocation) Then
MsgBox "Please enter in a location for this bid.", vbInformation, "Missing Location!"
Me.txtLocation.SetFocus
GoTo Done
End If
'Check to see if the quote number has been entered
If IsNull(Me.txtQuote) Then
MsgBox "Please enter a Quote Number for this bid.", vbInformation, "Missing Quote Number!"
Me.txtQuote.SetFocus
GoTo Done
End If
'Open Bid table to write selections to
rs.Open "tblBid", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
With rs
'Start loop to process all items selected in the list boxes
For Each varItem In Me.Create_Bid.ItemSelected
.AddNew
.Fields("QuoteNumber") = txtQuote
.Fields("CustomerName") = lstCust
.Fields("JobLocation") = txtLocation
.Fields("lstBathroom") = Me.lstBathroom.Column(6, varItem)
.Fields("lstToilets") = Me.lstToilets.Column(7, varItem)
.Fields("lstTub") = Me.lstTub.Column(8, varItem)
.Fields("lstVents") = Me.lstVents.Column(9, varItem)
.Update
End With
rs.Close
MsgBox "Your bid has been successfully created!", vbExclamation, "Bid Created"
DoCmd.Close acForm, Me.Name
GoTo Done
ErrorHandler:
MsgBox Err.Description
Done:
End Sub
I also want to find a way to check that a check box has been selected and run an error if both are unchecked.
Below is what I have so far. It's all being done at the save_click and writing to a table that has a column for each list box. There are a total of about 20 different list boxes but I'm just starting with a few to make sure I can get it first. The first one starts at column 7 on the table. Here's what I have so far....
Private Sub Save_Bid_Click()
Dim rs As New ADODB.Recordset
Dim varItem As Variant
On Error GoTo ErrorHandler
'Check to see if the location field is not blank
If IsNull(Me.txtLocation) Then
MsgBox "Please enter in a location for this bid.", vbInformation, "Missing Location!"
Me.txtLocation.SetFocus
GoTo Done
End If
'Check to see if the quote number has been entered
If IsNull(Me.txtQuote) Then
MsgBox "Please enter a Quote Number for this bid.", vbInformation, "Missing Quote Number!"
Me.txtQuote.SetFocus
GoTo Done
End If
'Open Bid table to write selections to
rs.Open "tblBid", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
With rs
'Start loop to process all items selected in the list boxes
For Each varItem In Me.Create_Bid.ItemSelected
.AddNew
.Fields("QuoteNumber") = txtQuote
.Fields("CustomerName") = lstCust
.Fields("JobLocation") = txtLocation
.Fields("lstBathroom") = Me.lstBathroom.Column(6, varItem)
.Fields("lstToilets") = Me.lstToilets.Column(7, varItem)
.Fields("lstTub") = Me.lstTub.Column(8, varItem)
.Fields("lstVents") = Me.lstVents.Column(9, varItem)
.Update
End With
rs.Close
MsgBox "Your bid has been successfully created!", vbExclamation, "Bid Created"
DoCmd.Close acForm, Me.Name
GoTo Done
ErrorHandler:
MsgBox Err.Description
Done:
End Sub