Solved

Current Record VBA

Posted on 2004-04-14
15
1,444 Views
Last Modified: 2009-12-16
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!!
0
Comment
Question by:huffine
  • 5
  • 3
  • 3
  • +2
15 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 10826791

It is because of this
Me.RemittanceID.Requery

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


0
 
LVL 2

Author Comment

by:huffine
ID: 10826862
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 10827051
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
 
LVL 39

Expert Comment

by:stevbe
ID: 10827076
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
 
LVL 2

Author Comment

by:huffine
ID: 10827329
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
 
LVL 7

Expert Comment

by:manthanein
ID: 10831532
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
 
LVL 39

Expert Comment

by:stevbe
ID: 10832709
"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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Author Comment

by:huffine
ID: 10833020

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
 
LVL 39

Expert Comment

by:stevbe
ID: 10833732
"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
 
LVL 54

Accepted Solution

by:
nico5038 earned 250 total points
ID: 10834822
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
 
LVL 2

Author Comment

by:huffine
ID: 10837078
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
 
LVL 7

Expert Comment

by:manthanein
ID: 10839247
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
 
LVL 2

Author Comment

by:huffine
ID: 10842541
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
 
LVL 7

Expert Comment

by:manthanein
ID: 10852128
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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now