problem with subform requery

[shapman]

Hi all

I have a problem locating where to put my requery for a subform and, also, how quite to type it. Here is the relevant information

the drop down list row source in the second combo ( named "2ndstatus") is dependent on th1 1st combo ("1ststatus"), this is done through a query that I have built. I need the row source to requery each time the selection is made, bear in mind that the subform is in datasheet view. I dont know whether to put on the afterupdate of the 1ststatus (the control source) or the 2ndstatus (the dependent source). And also whether to put this in the coding of the main form or the subform or, indeed, both. further relevant information:

the form is saved as "subfrmstatus" within the main form it is named 'child127'.

the query which sets the row source is saved as 'qrysubstatus'.

I'm not sure whether i should be requerying the 1ststatus, 2ndstatus, both. whether it shuold be the main form or the sub form.

Also, there is an afterupdate for cbo1ststatus but not for 2ndstatus, there is just a ctl2ndstatus.

Help! im quite lost. I think i may be making this harder then it needs to be!

thanks in advance, help is greatly appreciated, i very much need to get past this problem.

 

[boblarson]

You can put the requery in the After Update event of each combo box (and should):

Forms!YourMainFormNameHere.child127.Form.Requery

 

[boblarson]

And this might help you with how to use subform syntax:

1. When you are dealing with subforms, you are actually dealing with two parts - the subform CONTAINER (that which houses the subform on the main form) and the subform itself.

2. The subform and subform container can be named the same, but are not necessarily so. You need to check before writing the code. If they are the same then it simplifies things but it doesn't really matter if it is, or isn't, because you just have to refer to the container.

3. When you are doing things like requerying the subform, you are not really requerying the container, as it doesn't have a requery method, but the subform itself does. So, when you are referring to a property, or method, on the actual subform (not the container), you need to have the subform container name and then .Form. between the container name and the method, or property, so Access knows you want to refer to the form's method or property and not the container's method or property.


and here's a reference for you:

http://www.access-programmers.co.uk/forums/showthread.php?t=127981

 

[shapman]

Requery wont work in subform.

Hi bob

thankyou for your reply, I understand what you mean by when it is in the main form you need to add .form so the database knows to requery when in the main from. However, I still can't seem to get it working. I have tried variation after variation of the above code and can't get it to fire up. again, the answer may well be staring me in the face but i can't see it.

Here is the breakdown of the names of the different files:

Main form saved as: frmGeneralContactsDetails.
Subform saved as: subfrmstatus
combo box one named: 1ststatus (do i need to put cbo in front of this when i type it in?)
combo box two named: 2ndstatus (this is dependent on combo box one through a query in the rowsource)
Subform object in Main Form named: Child127
Name of Query in combo two : qrysubstatus

The more I have been playing around with the code the more i am being confused. with the rowsource in 2ndstatus being dependant on 1ststatus do I concentrate the code on 2ndstatus or 1ststatus? i.e. do i requery 2ndstatus in both the 1ststatus and 2ndstatus 'after update' or solely requery the 1ststatus or, alternatively requery 1ststatus in its own after update and the 2ndstatus in its own afterupdate?

on to the code... i was wondering which one of the above is the control name? I would have assumed 1ststatus but when i think about the control of the row source is actually the qrysubstatus (this is what queries the contents of combo box one so could be seen as a control).

Here is one of many examples that I have typed into the 2ndstatus after update:

Private Sub Ctl2ndstatus_AfterUpdate()
Me.subfrmstatus_1stStatus.Form.Requery
End Sub


I have attached the main form, sub form and related tables (ive had to wipe the contacts from the DB and havn't included other subforms in it -though i have left a few minor details in there as examples) the general contacts table and form isnt finalised yet and im sure there wil be lots of changes before it is finished but have to get past this requery issue before i can move on. Il also be using requery for a email qry controlled through a form and I'm hoping that learning how to do this will help me move on quicker with other parts of the database.

Thank you so much for your help. it is very much appreciated.

Kind regards

Simon

 

[boblarson]

Okay, let's explain a couple of things here.

1. If you are using the requery in an event ON the subform, then you don't have to use anything but Me.YourControlNameHere.Requery. So, in this case you would have:

Me.1stStatus.Requery

You had used Me.subfrmstatus_1stStatus.Form.Requery (which is wrong not only because of the bit about it being on the subform, but also because you used an underscore instead of dot to refer to the control. If this code had been on the MAIN FORM, then you would have used:

Me.subfrmstatus.Form.1stStatus.Requery

Not only using a dot but also the .FORM part comes AFTER the Subform CONTAINER name, not the control.

 

[shapman]

thanks bob, ive done it!!!!! almost dancing round the office with relief! i had to rename combo boxes because access was expecting an = before 1ststatus so i just changed it to first and secondstatus.

then refereshed the code pane (deleted everything) and started again, with me.firststatus.requery and me.secondstatus.requery

can't thank you enough

Simon