Solved

Current Record VBA

Posted on 2004-04-14
15
1,465 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +2
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

730 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