Controlling a queriy from multiple Combo-boxes

[Lowlevel32767]

I've been thrown in at the deep end (in fact, I'm underwater as I write this) and asked to create an Access 2000 DB to hold site details for customer sites.

I have a form with two combo-boxes, one that holds our global code for all of the sites.. and the other that holds the customers local code for all of the sites

I have a table that ties global (our) codes to local (their) codes (one to one). And I need to be able to run a single query whose results are the global and local codes for a particular site whichever of the two combo-boxes is updated

So effectively you can use our code or their code to retrieve the info

This is no doubt really easy.. and probably covered in the helpfiles.. But unfortunately I don't know the first thing about Access 2000 [Seacaptain]Arrr!.. I don't know what I'm doing[/Seacaptain]

 

[LittleSmudge]

The main bit of info that you don't explain is WHERE IS THE DATA ?

In this "that ties global (our) codes to local (their) codes (one to one)." do you have all the rest of the data that you want to display on your form - or is that in another table ?
If it's in another table - how does that table link to the you mention above.


Once you have a way of identifying the PrimaryKey for the table that is bound to the form you're working on - and can define that PrimeKey based on the value in each of the combo boxes then you'll need:-

In combo box called cboGlobal put this code in the After_Update event

Private Sub cboGlobal_AfterUpdate()
Dim F As Form
Set F = Me
F.RecordsetClone.FindFirst "GlobalRef = " & {{Fn(cboGlobal)}}
If Not F.RecordsetClone.NoMatch Then
    F.Bookmark = F.RecordsetClone.Bookmark
Else
    MsgBox "No matching record"
End If 
End Sub

WHERE
GlobalRef is the unique field in the form's recordset
{{Fn(cboGlobal)}} is the "way to calculate" that unique number based on the combo box selection


Repeat the same code - suitable modified for cboLocal and you're home and dry.


'ope-that-'elps.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[Lowlevel32767]

The data is held in 14 other tables that are queried individually (one query per table) using the global site code (which hopefully will be the result of the query run by the combo-boxes)..

There is no direct relationship between them (as I said, I don't know the first thing about Access) - I'm sure there's a cleverer way to do that too.

All I really need is the result of the query to be two fields global & local that I can use to run the other queries... I think...

[Seacaptain]Arrr!.. I don't know what I'm doing[/Seacaptain]

 

[LittleSmudge]

Right, So you have this table that links the Global and Local - which at a guess is something like:-

tblLink
GlobalRef Joint PrimeKey & FK to GlobalData
LocalRef Joint PrimeKey & FK to LocalData



In cboGlobal :-
RowSource = "SELECT GlobalId, GlobalSiteName FROM someTableHoldingThisData"
ColumnCount = 2
BoundColumn = 1
Column Width = 0;

Then the code in the earlier post goes in the AfterUpdate event.


In cboLocal :-
RowSource = "SELECT GlobalRef, LocalSiteName FROM tblLink, tblLocalData ON tblLink.LocalRef = tblLocalData.LocalId"
ColumnCount = 2
BoundColumn = 1
Column Width = 0;

This too will return the Global value that you can use in the same way.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D