2 combo boxes but need an afterupdate

[penwood]

There are 2 combo boxes: cboFlcMajorID and cboFlcMinorID.
Their underlying table fields are text. their answers are like 015 or 022 or 777.

Unfortunately, the company started their codes with zero.

I have the 2 combo box in sync. No problem there.

I don't know how to make the records fire up. I think I need an After Update in the second combo box.

In the 1st combo cboFlcMajorID, I have an After Update of:

Private Sub cboFlcMajorID_AfterUpdate()
Me.cboFlcMinorID = Null
' Requery the Flc Minor Pick combo
Me.cboFlcMinorID.Requery
End Sub

In the 2nd combo box cboFlcMinorID, is where i am stuck.I bet that i need an AfterUpdate here in order for the records to come up.

For example, if i choose 015 for the major and 010 for the minor, i would like to see 4 records appear. I use a Reset button to bring me all records again.

Can you help me? I put a short file on this File Dropper link. Thanks so much.

http://www.filedropper.com/2comboneedsafterupdate

 

[John Big Booty]

Please re-zip your DB and upload it with your next post (not in an off site link) Use the paperclip icon at the top of the posting window.

I was unable to extract the file as posted.

 

[penwood]

Hi John - found the manage attachments spot. Here is the shorty file for testing. I figured out now that my attachment could not be zipx but zip. Thanks for your patience in helping me. thanks Penwood

 

[missinglinq]

...Here is the shorty file for testing.

Where?

Linq ;0)>

 

[John Big Booty]

Simply put your requery command in the On Change event of cboFlcMajorID combo box.

Private Sub cboFlcMajorID_Change()
    
    Me.cboFlcMinorID.Requery
    
End Sub

 

[penwood]

John - I did what you said. I took out the afterupdate in cboFlcMajorID and put your code instead to the On Current Event.
Then saved and got out. Went to the frm2074MainItems main form.
Went to the yellow Major lookup field. selected 022 and then went to the Minor lookup field and picked 003. The 473 records did not change. I should get 34 records filtered.

Can you look again?

 

[John Big Booty]

OK sorry my bad I only half read what you were trying to do.

Have a look at the sample I posted here in this thread, I think you will find it is doing pretty much exactly the same thing as what you are after.

 

[penwood]

Re: 2 combo boxes but need an afterupdate - new file

John - Your Frt combo file is not exactly what I am looking for.

If you don't mind looking again, or anyone, I revised my file.

I threw in a new lookup field .... cboFlcMajor55 ......
It's purpose is to select ONE major code and then all records pertaining will show up. For example, if you picked 022 in just the purple cboFlcMajor55 field, you would get 141 records of the total 474 records possible.

The key factor for this one lookup cboFlcMajor55 field is the simplicity.

Private Sub cboFlcMajor55_AfterUpdate()

If Me![cboFlcMajor55] = 0 Then
DoCmd.ShowAllRecords
Else
DoCmd.ApplyFilter , "[FLCidMajor] = Forms![frm2074MainItems]![cboFlcMajor55]"
End If

End Sub


I hit the Reset button to start a new lookup. Maybe later, I can figure a way to avoid having to hit Reset when i want to start a brand new lookup.

I am trying to do the same approach with those two GREEN labels lookups that started this all. In those 2 fields, i want to pick one major and one minor and have all the records show up.

Is there a way to combine the 2 fields or maybe i put into the query the combined values? If so, I just made a concatenated field named .... MajorMinor .... with an answer like this ... 022-003

Would that help in making an easier After Update in cboFlcMinor ??
and then maybe do a similar afterupdate like the one noted above?
in simple novice vba?

 

[penwood]

John - I thought of something. In the cboFlcMinor, I could always make a similar MajorMinor calculated field into the row source of cboFlcMinor.

I already have MajorMinor in the form's data area. (I plan to later make it invisible.)

And then use the afterupdate coding that i showed above.

What do you think? It might work.

 

[penwood]

new version 3

John or whomever. I am working faster on this. I have to get this done.
So I made a new version 3. Still does not work but I feel that i am getting closer and can describe better to you.

Please note my afterUpdate event in the cboFlcMinorID lookup field.
Notice i concatenated a field on the row source. Then in the afterUpdate, I was hoping that this field would look to the form below and marry.

And pick up all records with the same MajorMinor. But they did not.
But maybe I am getting my idea across better now ?

newer zip file version 3 attached.

thanks

 

[penwood]

I unsuccessfully tried something in the lookup field cboFlcMinorID.
It gave me a debug. I was trying to concatenate the 2 lookup choices and then compare the MajorMinor field in the main form.


Private Sub cboFlcMinorID_AfterUpdate()

'Requery the whole Form
'Me.Requery

DoCmd.ApplyFilter , "[MajorMinor] = Forms![frm2074MainItems]![cboFlcMajorID]&" - "&Forms![frm2074MainItems]![cboFlcMinorID]"

End Sub

 

[penwood]

You can delete this question. I have my answer. thanks