Form to select which table to use

[weavind]

HI,

I would like to know if there is a way to have a form (checkbox or similar) select which table to use. My problem is that I am trying to add invoicing to my database, but the database has two companies linked to it. The invoices for company_1 is stored in table_A and the invoices for company_2 are stored in Table_B. Each company has to have unique incremental invoice numbers, so I can’t see how they can be merged into one table. I’d like the user to open the invoices form and select a checkbox to either load the invoices from company_1 or from company_2. I don’t know if there maybe is a different approach tot his problem that I haven’t thought of?

 

[Trevor G]

HI,

I would like to know if there is a way to have a form (checkbox or similar) select which table to use. My problem is that I am trying to add invoicing to my database, but the database has two companies linked to it. The invoices for company_1 is stored in table_A and the invoices for company_2 are stored in Table_B. Each company has to have unique incremental invoice numbers, so I can’t see how they can be merged into one table. I’d like the user to open the invoices form and select a checkbox to either load the invoices from company_1 or from company_2. I don’t know if there maybe is a different approach tot his problem that I haven’t thought of?

Add a form with a drop down where you use the company name, then add a command button and look behind the button at the events and select the On Click Event.

Go into the VBA and write some code like this:

If me.cboName.value="Company 1" Then
docmd.opentable "Table to Company 1"

Elseif me.cboName.value="Company2" Then
docmd.opentable "Table to Company 2"
Else
msgbox"You must select a company name first"
End if

 

[ghudson]

Users should not have direct access to the tables. You should change the record source of the form and I would use SQL as my forms record source.

Use a combo box for the user to select which table they want as their record source and in the After Update event of the combo box change the forms record source to the SQL based on the users selection.

Private Sub Combo0_AfterUpdate()

    If Me.cboName.Value = "Company 1" Then
         Me.RecordSource = "SELECT Branches.*FROM tblCompanyOne;"
    ElseIf Me.cboName.Value = "Company 2" Then
         Me.RecordSource = "SELECT Branches.*FROM tblCompanyTwo;"
    Else
         MsgBox "You must select a valid company!"
    End If

End Sub

 

[weavind]

Fantatastic! Exactly what I needed. I'm going to try the code now.

Thank you!