Setting TempVar

Question

Hi there, 

I'm setting up a database to keep track of inventory we receive from MANY different countries. The country is the most important as it's the first thing we use to start our searches. Within each country we then have 3 types of inventory (A, B, and C). This is the 2nd most important thing we search by.

When a user clicks a country button I want it to open a main navigation form. Within this main navigation form are 3 tabs with subforms for each of the 3 types of inventory, which also have datasheet sub(sub?)forms on each of these tabs (so essentially it's a split form for each inventory type). See image.

Originally, I had a frmMain for each country and that form was linked to a query for each type of inventory. This has become an issue because there are nearly 100 countries x 3 queries each (A, B, C) x 3 datasheets each (each inventory type has it's own loading form) x 3 split forms as you see above. If I need to make a design change I have to do it a minimum of 100 times. This was all before I learned about TempVars.

I'm trying to set TempVars on each country button so that when it is clicked and opens frmMain and only shows that countries particular inventory. 

Where do I put the tempvar? How do I specify the country of China? Am I not supposed to put it on the OnClick event of the button, and instead put it on the OnLoad on the frmMain? Or would I put it on the cboCountry field instead? I'm not even sure what to type.

Macro:

Name - tmpChina

Expression - [cboCountry]

But when I put =[TempVars]![tmpChina]![China] into the query the form loads blank.

If I get it to work, would I have to do ^^^ for every country in the query?

VBA:

[SetTempVar]![tmpChina]

Then I started playing around with the DoCmd and that didn't work:

DoCmd.OpenForm "frmMain", , "[CountryID]=" & Me![cboCountry]![China]

I very confused. Please help!! Much appreciated.   

<The thread has been moved to the correct category by forum moderator>

Answer 1

You only need the one main form for this, whose RecordSource returns all countries.  In this include an unbound combo box, named cboGoToCountry say, in which you select the country you wish to navigate to.  The combo box would be set up along the following lines:

RowSource:     SELECT CountryID, Country FROM Countries ORDER BY Country;

BoundColumn:   1

ColumnCount:    2

ColumnWidths:  0cm

If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

In the combo box's AfterUpdate event procedure put code like the following:

    Const MESSAGETEXT = "No matching record"

    Dim ctrl As Control

    With Me.RecordsetClone

        .FindFirst "ContactID = " & Nz(Me.cboGoToCountry ,0)

        If Not .NoMatch Then

            ' go to record by synchronizing bookmarks

            Me.Bookmark = .Bookmark

        Else

            MsgBox MESSAGETEXT, vbInformation, "Warning"

        End If

    End With

The inventories for the current country can be shown in three subforms in the form, each linked to the parent form by setting the LinkMasterFields and LinkChildFields properties of the subform control to CountryID.

To save space you can put each inventory subform on the last three pages of a tab control within the parent form.  The primary data in the parent form's recordset which identifies the country can be shown in bound controls above the tab control, so that it is always visible, while the subsidiary data in the main form's recordset can be shown in controls on the first page of the tab control.

To keep the unbound combo box in sync with the form if the user navigates to another country other than via the combo box, put the following code in the parent form's Current event procedure:

    Me.cboGoToCountry = Me.CountryID

You'll find an example in FindRecord.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes an option for opening a 'Tabbed Form' set up in the way described above.

Answer 2

How do you have the tables set up in the database?

I would expect that you would have a tblCountry with the Country Name and a CountryID field as a minimum and you would have a tblInventory which would contain columns for CountryID, InventoryName and InventoryID and a tblInventoryItems that would have columns for InventoryID and InventoryItem

Then on a form you would have a combobox that was loaded with the data from the tblCountry and when a country was selected the CountryID for the country would be used as a criteria to determine what was then loaded into and Inventory combobox and then when an item was selected from that combobox, the corresponding inventoryID would be used as the criteria for displaying the relevant Inventory items in another combo box and when and inventory item was selected, the details of that item would be displayed

Then you just have one form with a series of cascading controls.