How do I synchronize my combo boxes from different tables?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've read the listing preposted- but I am trying to get a combo box in a
subform to filter based on the selection of another combo box in the same
subform. I'm very new at VBA coding (so I pretty much am copying what I see
on here) - I have 3 tables 1) Process table - fields: process id, process
description 2) Productivity table - fields: producitvity code, productivity
description, process id 3) Productivity Entry table - fields included process
id and productivity code. I want to be able to update the Productivity Entry
table from my form, but when a process is selected, I only want the
productivity codes with that process code to show. I got this working with
a parameter query - but then it didn't let me update the form any more. Now
I'm pretty much back at square one. Thanks in advance for any help.
 
FergusonH said:
I've read the listing preposted- but I am trying to get a combo box in a
subform to filter based on the selection of another combo box in the same
subform. I'm very new at VBA coding (so I pretty much am copying what I see
on here) - I have 3 tables 1) Process table - fields: process id, process
description 2) Productivity table - fields: producitvity code, productivity
description, process id 3) Productivity Entry table - fields included process
id and productivity code. I want to be able to update the Productivity Entry
table from my form, but when a process is selected, I only want the
productivity codes with that process code to show. I got this working with
a parameter query - but then it didn't let me update the form any more. Now
I'm pretty much back at square one. Thanks in advance for any help.
Click to expand...
 
Hi Ferguson

The code is pretty straightforward. However, you need to be able to produce
SQL using the VB editor. I will assume you can do this for the time being and
concentrate on the code.

Let's say that the user has selected a process in the process combo box. You
now want the productivity combo box to show productivity records related to
the process id selected in the process combo box.

You do this by putting some code in the process combo box AfterUpdate event.
This means that whenever the process combo box is updated, this code gets run.

Bring up the properties box for the process combo box. Select the Events
tab. Select the AfterUpdate box. Click the little button with the three dots.
Choose 'Code Builder'. Insert the code between the Sub and End Sub lines.

The code for this would be:

[Productivity combo box].RowSource = " the SQL code you've got which shows
productivity records related to the chosen process "

Obviously, substitute 'Productivity combo box' above for whatever name
you've actually given this combo box in its Properties.

The easiest way to produce the SQL is to create a query that retrieves the
appropriate records [i.e. the ones you want in the combo box] in the query
editor, look at SQL View, and copy the gubbins in there.

Visual Basic does not like parameters in SQL. For example, if the SQL was:

"SELECT [Productivity table].[Field 1] FROM [Productivity table] WHERE
[Process ID] = [Process combo box]"

You might need to change it to:

"SELECT [Productivity table].[Field 1] FROM [Productivity table] WHERE
[Process ID] = " & [Process combo box]

When setting it as the row source. Note that the parameter has been removed
from the quotes.

Hope this helps in some small way.

Cheers

David
 
Back
Top