Current Record VBA

I have a subform:
frmMasterOfficesub

On it I have 2 combo boxes, when choosing something in the first combo box, it generates the list you can choose for the second combo box.

I have on the update procedure of the first combo box (officeID):
Me.RemittanceID.Requery

RemittanceID is the second combo box, and is requeried to show the list that is generated from the first combo box, like stated above.

This works fine in a single form, however, when I put it in a subform (many form) it requeries all remittanceID combo boxes for that subform hooked to the main form.  I just want it to requery the CURRENT RECORD combo box (remittanceID)

Does anyone have a VBA code or idea that will allow me to only requery the current record remittanceID after selecting the OfficeID combo box?

Greatly appreciate the help!!
LVL 2
huffineAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
nico5038Connect With a Mentor Commented:
I stopped useing cascading combo's on a continuous form as it's hardly impossible to get them working intuitively.
I use the continuous for for the selection of the "update row" and
1) have a button to open an update form or
2) place another single subform that's synchronized with the current row
3) place the combo's on the mainform with an update button to place the changes on the subform.

Not ideal, but also not giving headaches :-)

Nic;o)
0
 
Rey Obrero (Capricorn1)Commented:

It is because of this
Me.RemittanceID.Requery

If you put them on a subform
Me.SubformName.Form.RemittanceID.Requery


0
 
huffineAuthor Commented:
Receive a:
Method or data member not found
and it points to the subformname.
I have:
Me.frmMasterOfficesub.Form.RemittanceID.Requery

Really though it isn't a Me code right, because the Me would be the frmMasterOfficesub...since the combo box update resides on the subform itself.??  Am I thinking straight here.

Usually when you start typing code the auto-list will appear also, to help you with your selection/typing, that doesn't even happen when i type your suggestion in.
Am I doing something incorrectly of your suggestion?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Rey Obrero (Capricorn1)Commented:
Are you doing the codes with your subform on design view?

this is use when your subform is on the Main form which will represent {Me}
Me.SubformName.Form.RemittanceID.Requery
0
 
stevbeCommented:
1. If your subform is continuous then the combobox requery will always occur for all comboboxes.
2. When referencing a subform you need to reference the subform control name and not the name of the subform itself.

Steve
0
 
huffineAuthor Commented:
Yes I am doing the code in the design of the subform.....actually the design of the main form, and then going to the subform.
But the code and combo box is not on the main form, so the ME would be the subform in this instance.

stevbe:
1.  Yes that is my problem and question on how to just requery for the current record combo box.
2.  referencing the subform control name....are you saying remittanceID, that is the control name?  So the code I had Me.remittanceID is the call I need....however, I need the call to tell it just for this current record.  So something like:
me.remittance.requery.currentrecord or something in that order.  But I am not sure how to go about that.

0
 
manthaneinCommented:
hi,


     You could try editing the record source of the combobox RemittanceID. this will work if there is a key field in both the main and sub form..  try clicking the Row Source property for the combo box then a Query design view will appear.. right click on criteria, select build then  point to the control or the field in the main form where you want to reference it..   that way when you changerecord at the main form, the  criteria also changes to the current..


regards,

PS.  if it's too vague, just let me know
0
 
stevbeCommented:
"my problem and question on how to just requery for the current record combo box." <--  You cannot.

You could add a textbox to the form that sits on top of the combobox to hold the value stored in the record and then in the got focus event of the textbox, switch the combobox visible = true and set focus to combobox and then in the lost focus of the combobox switch them back. You would stuill do the requery iof the combobox for each occurence of the current event.

" referencing the subform control name"

Me.SubformControlName.Form.ComboboxName.Requery

Me = main form
.SubformControlName =  the name of the subform control on the main form and not the name of the embedded form itself
.Combobox name = the one you want to requery


Steve
0
 
huffineAuthor Commented:

Both combo boxes are on the subform.

manthanein, if I understand your directions, I am doing that already.  The first combo box is just a straight record source query.  The second combo box (remittanceID) has a record source with the criteria of the first combo box, hence why I want to requery this second combo box when selecting the first combo box.

Steve, Please tell me why I would call the ME, meaning the main form, when both comboboxes are on the subform.  The code is on the subform. On Update event for the first combo box.  Please also explain the subformcontrolname....what is the subform control, where do I find that?
My main form is:  frmMaster
My subform is:  frmMasterOfficesub
First ComboBox:  OfficeID
Second ComboBox:  RemittanceID

Thanks everyone for their help.
0
 
stevbeCommented:
"when both comboboxes are on the subform" I missed that in your post, there is no need for the subform reference at all.

Steve
0
 
huffineAuthor Commented:
Well, that is a bummer, because I have quite a few databases that I want to do that too!!  boo hoo!!

The one I am currently working on, I made a quick change, using a listbox on the main form, and then opening a single form with the two combo boxes that is generated.  Seems to work fine...but still bummed I cannot do a current record quick fix on code.

Thanks everyone!
0
 
manthaneinCommented:
huffine,
      you got the first part right..   you are missing something... instead of just having a record source with the criteria of the first combo box, add another criteria that is linked in a control or a field in the main form..  
0
 
huffineAuthor Commented:
manthanein, please tell me what that would be.  I keep asking what you/Steve are calling a control, "Please also explain the subformcontrolname....what is the subform control, where do I find that?"

I really can't use any fields in the main form, because they all apply to each record in the subform....ie. primary key (MasterID).

So please tell me what I am missing...
0
 
manthaneinCommented:
can I have a request..
please  post the  SQL for the  recordsource of the  2nd control box..
then  also post the SQL for the main form..

0
All Courses

From novice to tech pro — start learning today.