Private Sub no longer working after DB split

[yoshimura]

Hello everyone,
I'm having an issue with my database where a part of the script no longer seems to work after I split the database.
I used the manual split procedure:
* 1 empty database to become the Back end, import all tables
* 1 empty database to become the Front end, link to the Back end tables and import all but the tables

So far everything seems to be working, except 1 part.

The error I receive is captured in the attachment.
The relevant code is this:

Private Sub brandbox_AfterUpdate()
    modelbox.RowSource = "select distinct tbl_devices.id, tbl_devices.model from tbl_devices, tbl_packs, tbl_portfolio_mobile where tbl_devices.id = tbl_portfolio_mobile.id and tbl_devices.id = tbl_packs.id and tbl_devices.brand = '" & brandbox & "'" & " order by tbl_devices.model "
    
    packbox.RowSource = ""
    packbox.Value = ""
    
    modelbox.Requery
    packbox.Requery

End Sub

The code is the "After Update" [Event Procedure] of a combo box.
This code works when the database is not split.

FYI
I'm not a VBA programmer, nor am I the creator/owner of the database, but I do have programming/IT background and I'm supposed to take care of the database now (that's the scary part) since the creators are no longer available.

Thanks for your help and feedback.

 

[DCrake]

If you were to use this syntax as the SQL statment for a query do you get an error?


Try

TmpSql =   "select distinct tbl_devices.id, tbl_devices.model from tbl_devices, tbl_packs, tbl_portfolio_mobile where tbl_devices.id = tbl_portfolio_mobile.id and tbl_devices.id = tbl_packs.id and tbl_devices.brand = '" & brandbox & "'" & " order by tbl_devices.model "

Debug.Print TmpSql

Copy tmpsql to buffer via the immediate window and paste into a new query.

Dabid

 

[DCrake]

Private Sub brandbox_AfterUpdate()
    modelbox.RowSource = "select distinct tbl_devices.id, tbl_devices.model from tbl_devices, tbl_packs, tbl_portfolio_mobile where tbl_devices.id = tbl_portfolio_mobile.id and tbl_devices.id = tbl_packs.id and tbl_devices.brand = '" & brandbox & "'" & " order by tbl_devices.model "
    
    packbox.RowSource = ""
    packbox.Value = ""
    
    modelbox.Requery
    packbox.Requery

End Sub

Change to

Private Sub brandbox_AfterUpdate()
Dim TmpSQL As String

    TmpSQL  = "select distinct tbl_devices.id, tbl_devices.model from tbl_devices, tbl_packs, tbl_portfolio_mobile where tbl_devices.id = tbl_portfolio_mobile.id and tbl_devices.id = tbl_packs.id and tbl_devices.brand = '" & brandbox & "'" & " order by tbl_devices.model "
    
Debug.Print TmpSQL

modelbox.RowSource = TmpSQL

    packbox.RowSource = ""
    packbox.Value = ""
    
    modelbox.Requery
    packbox.Requery

End Sub

Open the form and change the combobox value to innitiate the After Update event.

Close the Form
Press Ctrl+G to open the immediate window and you should see the line of code that is the string derived from TmpSQL

Highlight this code and open a new query, don't select a table just press close.

In the top left corner of the tool bar you will see a button SQL click on that and paste in the code. Then try to run the query.

Any errors should be evident here. Identify, Correct and recode.

Repeat until working.

David

 

[yoshimura]

I keep getting the same error popup box after trigger the on update event:

"The expression After Update you entered as the event property setting produced the following error: User-defined type not defined."

I put a watch on TmpSQL and the comment I see is "expression not defined in content".

I don't see anything in the immediate window.

UPDATE:
When I remove all code from the Private Sub, it keeps giving me the same error.

The code for the form starts with:
Option Compare Database
Option Explicit
Prive Subs ...

Does anything have to be added here?

 

[yoshimura]

SOLVED

It appears that that VB references are not copied when you import tables/forms/queries/modules etc in order to create a split database.

Thanks for the support.