Need to update subforms from a query

[Pyrrhonist]

Having a problem getting my page to refresh the subforms the way I want them to The following link will show the basic front end I'm working with

http://deepblue.cs.camosun.bc.ca/~cst01046/SSG.JPG

SSG File, Reserve, Ship Code and Project are all unbound text boxes. You can do a search, by entering your string into whichever text box you want, then querying with the 'binocular' search button. My query string builds fine, and the qryResults table populates fine too, BUT, what I WANT to happen, is real time updating within the Subforms which are placed on the table layout below the search. I have three subforms, which display different parts of the same row from the same table. The three subforms are linked to the same form that the main form is using as a control source. ANY suggestions would be GREATLY appreciated, as I've tried TONNES of different options, but to no avail

Tried using me.requery, Forms!frmName!subfrmName.requery, ummm...and a few others, but it wont requery One thing to note, is that I am NOT using a recordset to store the values in...not sure if that makes a difference or not, but the values are being calculated and placed into a new qry, which is the source for the subforms. Umm....this is getting long, so if you have any suggestions, or have any further questions you need, PLEASE get ahold of me Thanks in advance for any advice you can give

 

[jsolutions]

Is the query results table the datasource for the subform?

What is the datasource for the main form and what is the primary key? Is it SSG File number?

If this is correct, since the subforms are tied to the main form through a field (I am guessing SSG File), you will need to search the main form for the record that represents that file. So you will probably need to attach this to the events for the binoculars. You will still need to requery the subform if you are regenerating the data.

 Me.RecordsetClone.FindFirst "[SSGFileId] = " & Me![SSGFileID]
 Me.Bookmark = Me.RecordsetClone.Bookmark

In other words, it will search for the record which matches the value in the unbound textbox for SSGFileId. It will then go to that record, which in turn will update the subforms.

Let me know if this is close to what you are looking for. Any more detail on what you are trying to do might be helpful too.

Jay

 

[Pyrrhonist]

Thanks for the prompt reply Jay....

Yes, the qryResults is the datasource for the main form...the nav buttons on the top are used to work through the results which get displayed.

The subforms are connected through link child and link master fields with SSG File # (A seperate issue I'm having, is that the datasheet view of the 'Browse' page does not display all results when I have that page linked with child link and master link pointing to the ssg file #....but I digress) I've posted the code at

http://deepblue.cs.camosun.bc.ca/~cst01046/Form_frmSSG_Jobs.txt

I have the requery linked to the search button at present, but I do this by calling another sub, which builds the qry string, then returns that value....kinda hack and slash code, but it SORTA works...problem like I said, is that I'm not using a recordset to do the query...so once again, any and all suggestions are GREATLY appreciated And once again, thank you for the prompt reply...

 

[jsolutions]

Sorry for the delay in replying. I have been out a few days.

In looking at your code, I have a couple of questions. qryTBL_JOBQueryNew is where you are building your query string right?

In your prior post, you said that qryResults is the datasource for the main form. Are you using qryResults as a general term or is this a different query?

From looking at the form you posted and the code you posted, my suggestion (if you don't mind backing up a little) is that your main form should be based on the table tblSSG_JOB. Then your subform should be based on qryTBL_JOBQueryNew. When you rebuild your query string, you will need to requery the subform AND seach the main form for the record matching the SSG File # since this is the link between main form and subform. When you search for the record for the SSG number in the main form, the requeried subform will then pull all the data relating to that SSG#.

I may be running you around in circles here and I don't mean to, just trying to get to the solution.

Let me know if this makes any sense.

Jay