updating a form based on the values of another (seperate) form

[markjkubicki]

I have 2 forms
the primary form MUST be a continuous form
on it is a command button to open a second form (if the user thinks it is necessary...)
the second form is essentially a subform of the first that displays multiple records related to a field in the current record of the main, first form

the 2nd opens with this code

stDocName = "FrmManufacturerAddlCatagoryEntry"

stLinkCriteria = "[ManufacturerName]=" & "'" & Me![txtManufacturerName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


----------------------t
the problem is that when I change record focus on the first form, I can't get the data on the second to requery

on the 1st form I have this code:
Private Sub Form_Current()
If (SysCmd(acSysCmdGetObjectState, acForm, "FrmManufacturerAddlCatagoryEntry") And acObjStateOpen) = acObjStateOpen Then
With Forms![FrmManufacturerAddlCatagoryEntry].
.[lblManufacturerName].Caption = Me.ManufacturerName
.Requery
.Repaint
End With
End If

lblManufacturerName.Caption updates correctly, so I know I must be in the right area
maybe?

thoughts, and thanks in advance,
Mark

[ItsMe]

The current record in the continuous form will be the record that has focus. If the control is in the header, you will lose focus. Try a dbl click event in the text box that is relevant to the user. Another option is to place a control in the detail section.

[markjkubicki]

the control is in the header, band the intent is that the user clicks it only once; the 2nd form opens, and stays open as they navigate thru the records of the 1st form.

the intent is that when the focus of the 1st form changes to a different record, that the records in the 2nd form are update to be those where ManufacturerName (on the 2nd) = Me![txtManufacturerName] on the first.

this is to say:
it should behave just like a sub form on the first, but because I need to have the first as a continuous form, I cannot do... =:-(

[ItsMe]

I had to edit my post. If the control is in the header of the continuous form, the focus will be on the control and not on a specific record. So I do not see how even your stLinkCriteria is working to open the form.

The current event will work. In order for it to fire, the user has to click within the record. This is why I suggest a double click or button. What are you using to encourage the user to set focus on the record within the continuous form?

[ItsMe]

I think I am starting to see....

Looks like you are using an unbound textbox to pass the value of ManufacturerName to your stLinkCriteria.

Here is the thing. When the second form opens, it is only going to show a portion of the records in the table or query. The where criteria is limiting the records in the recordset to this

stLinkCriteria = "[ManufacturerName]=" & "'" & Me![txtManufacturerName] & "'"

if the value of ManufacturerName changes, it will not be found in the second form's recordset. The second form's recordset is limited by the where criteria in the docmd.

You would have to reassign the recordsource of the second form to the original SQL and apply the new where criteria as the focus changes within the first form.

If you post the SQL for the original query without the Where clause, we can probably figure something out

[markjkubicki]

the control is in the header, and it does work (not experienced to know why... but it does)

about the current event, you're right, it does work; I know this because the first line of code works spot-on (it the second, where I ask the form to requery, that doesn't work
With Forms![FrmManufacturerAddlCatagoryEntry].
.[lblManufacturerName].Caption = Me.ManufacturerName

could this be because it's query is in its open statement, and as such, there is actually no query inherent in the form to "requery"?
stLinkCriteria = "[ManufacturerName]=" & "'" & Me![txtManufacturerName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

if this is the case...
Do you thin if I add a criteria to the forms record set, that do the trick?
=forms![FrmManufacturerAddlCatagoryEntry].[txtManufacturerName]

[markjkubicki]

well...
I just tried that and it didn't work at all
even when the 2nd form opened for the first time, its records weren't correctly filtered

[ItsMe]

Totally have to reassign the recordset to the second form. What is the second form based on? A query or a table? If it is a query, can you post the SQL?

[markjkubicki]

it is a table

SELECT MfrCatagory.ManufacturerName, MfrCatagory.Catagory
FROM MfrCatagory
ORDER BY MfrCatagory.Catagory;

[ItsMe]

Maybe something like this in your current event will do the trick. Can't say I have ever tried doing something like this though.

Dim strSQL As String
strSQL = "SELECT MfrCatagory.ManufacturerName, MfrCatagory.Catagory " & _
         "FROM MfrCatagory " & _
         "WHERE ManufacturerName = '" & Me![ManufacturerName] & "'" & _
         "ORDER BY MfrCatagory.Catagory;"
If (SysCmd(acSysCmdGetObjectState, acForm, "FrmManufacturerAddlCatagoryEntry") And acObjStateOpen) = acObjStateOpen Then
    With Forms![FrmManufacturerAddlCatagoryEntry]
        .[lblManufacturerName].Caption = Me.ManufacturerName
        .RecordSource = strSQL
        .Requery
        .Repaint
    End With
End If

[markjkubicki]

YAHOOOO !!!!

big thanks (that did the trick!)

[ItsMe]

Good News!