Filter Combo Box Based on Previous Combo Box Selection 2

[TonyBerry]

I have searched and searched for an answer and have found a lot of good information, but nothing to address my issue. I am a big fan of DLookup, but I don't think I can get it to do what I want in this case.

Database Layout: frmMain, frmModel, tblMain, tblModel, qryManufacturer

The first field in frmMain is bound to tblManufacturer (Dell, IBM, Cisco, etc.). These values are supplied by qryManufacturer which basically pulls all the unique fields from tblModel.Manufacturer. When selecting "Dell" in the first combo box then pressing Tab to go to the next combo box, I would like the second combo box to filter only the "Dell" items from tblModel (i.e. PE2950, PE6850, etc.).

Thanks for the help. I haven't done any Access/VBA programming in a LONG time. I'm a Network/System Administrator now, so I'm getting pretty rusty on this stuff.

 

[seaport]

Here is some sample code.

private sub SecondCombo_Enter()
    if FirstCombo & ""="" then
        msgbox "Please choose the manufacture!"
        FirstCombo.SetFocus
    else
       SecondCombo.RowSource="select Model from tblModel " & _
       "where Manufacturer='" & FirstCombo & "'"
    end if 
end sub

 

[TonyBerry]

And circle gets the square!

Worked perfectly and fast response as well.

Thank you.

 

[dhookom]

TonyBerry,
If "... circle gets the square!", you should award seaport a star by clicking the link [link javascript:tipvote(1586056,'seaport');]Thank seaport for this valuable post![/url]

Duane
Hook'D on Access
MS Access MVP

 

[TonyBerry]

Thanks for the note dhookom. I didn't even see the link until you pointed it out. I also didn't realize this was a point-based forum.

Star granted.

 

[dhookom]

The star awards a quality answer/contribution and marks a thread as being successfully answered.

Duane
Hook'D on Access
MS Access MVP

 

[seaport]

Glad it helped.

Duane, Thanks.

Seaport

 

[Mayhem9]

Hello,

Could someone provide a little explanation of the code please? I just tried applying this but I couldn't get it to work.

I don't have a specific use for it at the moment but it looks like a very useful feature and one that I would use in the future. I think I can see what the code does but I seem to struggle when trying to figure out which parts need to be replaced with the names of fields etc from my own forms.

Any help is greatly appreciated,
Darren

 

[dhookom]

Darren,
Last I checked, no one here could see your computer monitor.

Can you share your control names, code, data types of significant fields, ... "couldn't get it to work" doesn't tell us anything about your results.

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Last I checked, no one here could see your computer monitor.

Sorry – I don't actually have a use for it now but I looks like something that could be very useful. When I say I couldn't get it to work I was trying to replicate what had been outlined by Tony in his database.

What I generally have trouble with in using code is figuring out which parts to change. I guess what I was asking is for some assistance on how the code works so I can then try to apply it myself.

 

[dhookom]

The red expressions are all names of controls, fields, and tables that should be changed depending on your situation. You would also need to understand the difference between strings and numeric data types. Manufacturer in this code is a string/text. I would probably add an ORDER BY to the Row Source SQL.

private sub [red]SecondCombo_Enter[/red]()
    if [red]FirstCombo[/red] & ""="" then
        msgbox "Please choose the [red]manufacture[/red]!"
        [red]FirstCombo[/red].SetFocus
    else
       [red]SecondCombo[/red].RowSource="select [red]Model[/red] from [red]tblModel[/red] " & _
       "where [red]Manufacturer[/red]='" & [red]FirstCombo[/red] & "'"
    end if
end sub

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Thanks Duane,

I will see if I can get ths working. I have filed the code away for future use.

Cheers,
Darren

 

[Mayhem9]

Hello,

I have set up the table and form fields and now that I am testing the code (exactly as posted above)I am getting the following error:

Compile error:

Member already exists in an object module from which this object module derives.

I’m unsure as to what this means. I’m looking for an answer to help me try to figure it out, as opposed to posting my code and getting it fixed for me.

I commented out the other code I had for either combo box (double click to open a form, error if text not found and FAYT combo box) but the error still persists.

Cheers,
Darren

 

[dhookom]

It sounds like you might have commented out some code but not the first line like:

private sub SecondCombo_Enter()

Did you compile your code after you modified it? This should always be the first task after updating any code.

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Thanks Duane,

Didn't know that. What does compiling the code do?

G can't that I missed anything but will look in the morning with fresh eyes. Ctrl f didn't find it either.

Cheers
Darren

 

[dhookom]

Compiling your code will find syntax errors (not logic errors).


Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Thanks,

I had a look and there is an option under the debug menu for Compile TL. I clicked it and nothing obvious happened (I am taking this as a good sign). When I looked again, this option was no longer active.

I assume this is what you meant by compile my code, as I couldn't find any other options and the help in MS Access didn't live up to its name (or perhaps it was my search string).

I'll try to remember to do this from now on if I alter the code.

Cheers,
Darren

 

[dhookom]

What search options did you use?

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Hi Duane,

I didn't search the forum, I tried my luck with MS Access Help

"Compile code" returned 30 results.

One spoke of compile-type errors being one of three types (logic and run-time being the other two). But other than that, I didn't see anything that actually covered compiling code.

This is the type of entry that amazes me about these help sections:

When I run my code, Microsoft Access displays messages that I don't want to see.

You can temporarily stop the display of warnings and other messages while Microsoft Visual Basic (Microsoft Visual Basic: A high-level, visual-programming version of Basic. Visual Basic was developed by Microsoft for building Windows-based applications.) code is running by using the SetWarnings method to carry out the SetWarnings action. You can also use the SetWarnings method to restart the display of messages.

I would have expected something about what may be causing the messages and then perhaps something about how to fix them. Am I being unreasonable?

But my personal favorite is what they put under "See Also:"
- Try Office 2007
- Buy Office 2007
- Buy a suite of code tools from FMS Inc.

Cheers,
Darren

 

[dhookom]

I thought you were searching for duplicate procedure names. There are a few options when you do this.

I had searched Help on "Compile" and I think it came up as the 4th item in the list (Access 2007).

Duane
Hook'D on Access
MS Access MVP