Hello all,
I am not the most advanced user. However, I have had some help creating a combo box with the code below. I want to utilize a form to allow users to enter data. I was wondering if anyone has any suggestions on adding buttons, and the code therein, for adding a new record? In addition if anyone has suggestions on adding, more than one record at a time. For example a drop down that would allow a user to select up to 100 items to be added. I hope that was somewhat clear. Please let me know if I can clarify anything.
I am not the most advanced user. However, I have had some help creating a combo box with the code below. I want to utilize a form to allow users to enter data. I was wondering if anyone has any suggestions on adding buttons, and the code therein, for adding a new record? In addition if anyone has suggestions on adding, more than one record at a time. For example a drop down that would allow a user to select up to 100 items to be added. I hope that was somewhat clear. Please let me know if I can clarify anything.
Code:
Option Compare Database
Private Sub cboBrand_AfterUpdate()
'Update Selected Brand and Display
txtBrand.Visible = True
txtBrand.Value = CboBrand.SelText
'Update User Prompt
lblBrand.Caption = "Selected Brand"
'Display command button to Clear the Brand
cmdClearBrand.Visible = True
cmdClearBrand.SetFocus
'Hide Brand Selector Combo-Box
CboBrand.Visible = False
'Update Category Selection List
Build_Cat_ComboBox
Update_Item_List
End Sub
Private Sub cboCat_AfterUpdate()
'Update Selected Category and Display
txtCat.Visible = True
txtCat.Value = cboCat.SelText
'Update User Prompt
lblCat.Caption = "Selected Category"
'Display command button to Clear the Category
cmdClearCat.Visible = True
cmdClearCat.SetFocus
'Hide Category Selection Combo-Box
cboCat.Visible = False
'Update Brand Selection List
Build_Brand_ComboBox
Update_Item_List
End Sub
Public Sub Build_Cat_ComboBox()
Dim strSQL As String
'Only Rebuild Category ComboBox if a Category has not been selected
If txtCat.Value = "" Then
'Rebuild Category combo-box
'If a Brand is selected, list the Categories using this Brand
If txtBrand.Value <> "" Then
strSQL = "SELECT c.category FROM brands as b INNER JOIN"
strSQL = strSQL + " (MedicalSupply as i inner join categories as c on i.category = c.id)"
strSQL = strSQL + " ON b.ID = i.brand"
strSQL = strSQL + " WHERE b.brand = '" + txtBrand.Value + "' GROUP BY c.category"
Else
strSQL = "select category from categories order by category"
End If
'assign SQL string to combo-box
cboCat.Visible = True
cboCat.SetFocus
cboCat.RowSource = strSQL
cboCat.RowSourceType = "Table/Query"
cboCat.Requery
End If
End Sub
Public Sub Build_Brand_ComboBox()
Dim strSQL As String
'Only Rebuild Brand ComboBox if a Brand has not been selected
If txtBrand.Value = "" Then
'Rebuild brand combo-box
'If a Category is selected, list the Brands in this Category
If txtCat.Value <> "" Then
strSQL = "SELECT b.Brand FROM brands as b INNER JOIN"
strSQL = strSQL + " (MedicalSupply as i inner join categories as c on i.category = c.id)"
strSQL = strSQL + " ON b.ID = i.brand"
strSQL = strSQL + " WHERE c.Category = '" + txtCat.Value + "' GROUP BY b.Brand"
'If no Category is selected, display all Brands
Else
strSQL = "select brand from brands order by brand"
End If
'assign SQL string to combo-box
CboBrand.Visible = True
'CboBrand.SetFocus
CboBrand.RowSource = strSQL
CboBrand.RowSourceType = "Table/Query"
CboBrand.Requery
End If
End Sub
Public Sub Update_Item_List()
Dim strSQL As String
strSQL = "SELECT i.id, c.Category, b.Brand, i.Item"
strSQL = strSQL + " FROM Categories as c INNER JOIN"
strSQL = strSQL + " (Brands as b INNER JOIN MedicalSupply as i ON b.ID = i.Brand)"
strSQL = strSQL + " ON c.ID = i.Category"
'If either selection box is used, Query Item File
If (txtCat.Value <> "" Or txtBrand.Value <> "") Then
'Both Category and Brand Selected
If (txtCat.Value <> "" And txtBrand.Value <> "") Then
strSQL = strSQL + " WHERE c.Category = '" + txtCat.Value + "' AND b.Brand = '" + txtBrand.Value + "'"
End If
'Only Category Selected
If (txtCat.Value <> "" And txtBrand.Value = "") Then
strSQL = strSQL + " WHERE c.Category = '" + txtCat.Value + "'"
End If
'Only Brand Selected
If (txtCat.Value = "" And txtBrand.Value <> "") Then
strSQL = strSQL + " WHERE b.Brand = '" + txtBrand.Value + "'"
End If
Else
strSQL = strSQL + " order by i.item"
End If
'Assign SQL statement to combobox control
Me.lstItems.RowSource = strSQL
Me.lstItems.RowSourceType = "Table/Query"
Me.lstItems.Requery
End Sub
Private Sub cmdClearBrand_Click()
Dim strSQL As String
'clear and hide selected brand textbox
txtBrand.Value = ""
txtBrand.Visible = False
'change user prompt
lblBrand.Caption = "Select Brand"
'Rebuild Selection List
Build_Brand_ComboBox
'Remove focus and hide Command Button to Clear Brand
CboBrand.SetFocus
cmdClearBrand.Visible = False
'Reset Item Listing
Update_Item_List
End Sub
Private Sub cmdClearCat_Click()
Dim strSQL As String
'clear and hide selected category textbox
txtCat.Value = ""
txtCat.Visible = False
'change user Prompt
lblCat.Caption = "Select Category"
'Rebuild Selection List
Build_Cat_ComboBox
'Remove focus and hide Command Button to Clear Category
cboCat.SetFocus
cmdClearCat.Visible = False
'Reset Item Listing
Update_Item_List
End Sub
Private Sub Addnewrecord()
End Sub
Private Sub Form_Load()
'Initialize User Prompts
lblCat.Caption = "Select Category"
lblBrand.Caption = "Select Brand"
'Initialized and Hide Selected Value textboxes
txtCat.Value = ""
txtCat.Visible = False
txtBrand.Value = ""
txtBrand.Visible = False
'Hide Command buttons to clear selected values
cmdClearCat.Visible = False
cmdClearBrand.Visible = False
'Initialize Combo-Box Lists
CboBrand.RowSource = "select brand from brands order by brand"
CboBrand.RowSourceType = "Table/Query"
cboCat.RowSource = "select category from categories order by category"
cboCat.RowSourceType = "Table/Query"
'Clear Item List
lstItems.RowSource = ""
lstItems.RowSourceType = ""
End Sub
