Update 2 tables with 1 form in Access 2010


  • Hello all,

    I have been scouring the internet to no avail for an answer to what seems like it would be a simple process.  I want the selections in a form to have an effect on where the data goes.  So if the user selects a particular option from the combobox on the form, I want the date they enter in the text box to be put on one table or the other.  

    My setup: frmFood_Inspections is the form I input individual inspections; tbl2013Inspections tracks the inspections needed for each facility for the year; tblEstablishment tracks the business information and is linked with the Establishment field of tbl2013Inspections

    On frmFood_Inspections you select an establishment, input a date of your inspection, and select the type of inspection.  That information is stored on tblFood_Inspections by default.  

    I'm looking for something along these lines:

    If combobox.value = "Comprehensive" Then
         'Go to the record with establishment selected in tbl2013Inspections
         Dlookup("Comprehensive","tbl2013Inspections","Establishment field.value")
         ' add the date from txtInspectionDate into the Comprehensive field of tbl2013Inspections
        tables!tbl2013Inspections!Comprehensive = txtInspectionDate.Value
    ElseIf combobox.value = "Monitoring" Then
        tables!tbl2013Inspections!Comprehensive = txtInspectionDate.Value
    End If

    There has got to be a better/easier way to dynamically enter data based on the user's selections on a form.  Any help would be greatly appreciated.

    Thursday, January 31, 2013 9:25 PM

All replies