Add new record

[Gauchey]

I am a newer user. I have created a forms that includes combo boxes and is referencing a query. How would add a button to add the information to a table?

I have read in another post that it was not possible to add a new record if I was basing my form off of a query? Is that the issue I am running into?

 

[plog]

First, anything is possible with enough duct tape and VBA. Doesn't mean its a good idea or less work than doing things the right way, but lots of things are possible.

With that said, forms that interact with data (add/edit/delete) should be based on tables, not queries. You set the Record Source property of the form to your table and Access does the heavy lifting in getting your tables to update when you make changes using the forms.

So, my advice is to set up your forms to be based on tables. If you are having trouble with that, post an issues you have and we can help.

 

[missinglinq]

The only thing that I'd add to plog's advice is the fact that many experienced developers only base Forms on Queries, not on Tables...but only on Single-Table Queries! They feel that basing a Query on a Single Table and then basing the Form on the Query adds another layer of protection between the end-users and the data.

Allen Browne has an excellent article that explains why Multi-Table Queries are frequently Read-Only:

Why is my query read-only?

Linq ;0)>

 

[Gauchey]

Thanks for the responses. It is greatly appreciated. I feel like I am sort of stuck. I taught myself how to use Access, perhaps not very well, and feel like I have hit a wall.

I am trying to incorporate a combo box that cascades in order to narrow down a selection. After a user gets to the item, I would like to have a button that would allow them to save it to a sheet.

I was fortunate to have someone that is proficient in code write up the combo box VBA. However, I cannot for the life of me figure out how to add a selection to a table. I have included the code bellow. Should I start with a blank form and base it off of a table rather than the query? Or do you have suggestions on how I might add some code, a macro, or the like to add it to a table?

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 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 = True
    cmdClearBrand.Visible = True
    '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


Public Sub save()

End Sub