Master/Detail Form using a Query

[Robert]

I have a Master/Dtail Form that works just fine that is NOT based on a query.
On the other hand I tried to create the exact M/D form using a query but it
does not work (sort of). In the non-query version the record pointer for the
master table (one record) moves from record to record with no proble. On the
other hand the one based on a query steps through every "child" record before
it changes the master record. So my question is can you use a query in a
master/detail form?

 

[Graham Mandeno]

Hi Robert

Yes, you can, but you should not include detail information in the query for
your main form, only in the query for your subform.

Remember that a form is just a view of a recordset, so you will see one
record in your form for each record in the table or query that is your
recordsource.

 

[Robert]

If you make two separte queries how do get the record pointer to move in the
subform when it is changed in the master form?

Hi Robert

Yes, you can, but you should not include detail information in the query for
your main form, only in the query for your subform.

Remember that a form is just a view of a recordset, so you will see one
record in your form for each record in the table or query that is your
recordsource.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a Master/Dtail Form that works just fine that is NOT based on a
query.
On the other hand I tried to create the exact M/D form using a query but
it
does not work (sort of). In the non-query version the record pointer for
the
master table (one record) moves from record to record with no proble. On
the
other hand the one based on a query steps through every "child" record
before
it changes the master record. So my question is can you use a query in a
master/detail form?

 

[Graham Mandeno]

Hi Robert

The idea of a mainform/subform is *usually* to represent the records on both
sides of a one-to-many relationship. The "one" side (master) is displayed
in the main form and the "many" side (detail) is displayed in the subform.
So when you move to a new record in the main form, the entire recordset of
the subform changes.

Isn't this what you want? If not then please provide more information,
including your table and relationship designs.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

If you make two separte queries how do get the record pointer to move in
the
subform when it is changed in the master form?

Hi Robert

Yes, you can, but you should not include detail information in the query
for
your main form, only in the query for your subform.

Remember that a form is just a view of a recordset, so you will see one
record in your form for each record in the table or query that is your
recordsource.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a Master/Dtail Form that works just fine that is NOT based on a
query.
On the other hand I tried to create the exact M/D form using a query
but
it
does not work (sort of). In the non-query version the record pointer
for
the
master table (one record) moves from record to record with no proble.
On
the
other hand the one based on a query steps through every "child" record
before
it changes the master record. So my question is can you use a query in
a
master/detail form?

 

[Robgsem]

Hi Robert

The idea of a mainform/subform is *usually* to represent the records on both
sides of a one-to-many relationship.  The "one" side (master) is displayed
in the main form and the "many" side (detail) is displayed in the subform.
So when you move to a new record in the main form, the entire recordset of
the subform changes.

Isn't this what you want?  If not then please provide more information,
including your table and relationship designs.
--
Good Luck  

Graham Mandeno [Access MVP]
Auckland, New Zealand

If you make two separte queries how do get the record pointer to move in
the
subform when it is changed in the master form?

Hi Robert
Yes, you can, but you should not include detail information in the query
for
your main form, only in the query for your subform.
Remember that a form is just a view of a recordset, so you will see one
record in your form for each record in the table or query that is your
recordsource.
--
Good Luck  
Graham Mandeno [Access MVP]
Auckland, New Zealand
I have a Master/Dtail Form that works just fine that is NOT based on a
query.
On the other hand I tried to create the exact M/D form using a query
but
it
does not work (sort of). In the non-query version the record pointer
for
the
master table (one record) moves from record to record with no proble.
On
the
other hand the one based on a query steps through every "child" record
before
it changes the master record. So my question is can you use a query in
a
master/detail form?- Hide quoted text -

- Show quoted text -

Yes, that is exactly what I want and it is working perfectly except
for a new problem. I usually add my own buttons using the wizard for
navigation. Now I getting the following error msg:

The expression On Click you entered as the event property setting
produced the following
error: A problem occurred while Microsoft Office Access was
communicating with the OLE
server or ActiveX Control.

This error message occurs when I click on any of the buttons: Next,
Previous, Close ect. Any idea why is is happening?

 

[Graham Mandeno]

Hi Robert

[snip]

Yes, that is exactly what I want and it is working perfectly except
for a new problem. I usually add my own buttons using the wizard for
navigation. Now I getting the following error msg:

The expression On Click you entered as the event property setting
produced the following
error: A problem occurred while Microsoft Office Access was
communicating with the OLE
server or ActiveX Control.

This error message occurs when I click on any of the buttons: Next,
Previous, Close ect. Any idea why is is happening?

My crystal ball is a bit cloudy ;-) It's very hard for me (or anyone) to
guess if you don't post the code that is raising the error.

 

[Robgsem]

Hi Robert

[snip]

Yes, that is exactly what I want and it is working perfectly except
for a new problem. I usually add my own buttons using the wizard for
navigation. Now I getting the following error msg:

The expression On Click you entered as the event property setting
produced the following
error: A problem occurred while Microsoft Office Access was
communicating with the OLE
server or ActiveX Control.
This error message occurs when I click on any of the buttons: Next,
Previous, Close ect. Any idea why is is happening?

My crystal ball is a bit cloudy ;-)  It's very hard for me (or anyone) to
guess if you don't post the code that is raising the error.
--
 

Graham Mandeno [Access MVP]
Auckland, New Zealand

Understood the follows:


Private Sub btnFirst_Click()
On Error GoTo Err_btnFirst_Click


DoCmd.GoToRecord , , acFirst

Exit_btnFirst_Click:
Exit Sub

Err_btnFirst_Click:
MsgBox Err.Description
Resume Exit_btnFirst_Click

End Sub
Private Sub btnLast_Click()
On Error GoTo Err_btnLast_Click


DoCmd.GoToRecord , , acLast

Exit_btnLast_Click:
Exit Sub

Err_btnLast_Click:
MsgBox Err.Description
Resume Exit_btnLast_Click

End Sub
Private Sub btnNext_Click()
On Error GoTo Err_btnNext_Click


DoCmd.GoToRecord , , acNext

Exit_btnNext_Click:
Exit Sub

Err_btnNext_Click:
MsgBox Err.Description
Resume Exit_btnNext_Click

End Sub
Private Sub btnPrevious_Click()
On Error GoTo Err_btnPrevious_Click


DoCmd.GoToRecord , , acPrevious

Exit_btnPrevious_Click:
Exit Sub

Err_btnPrevious_Click:
MsgBox Err.Description
Resume Exit_btnPrevious_Click

End Sub
Private Sub btnClose_Click()
On Error GoTo Err_btnClose_Click


DoCmd.Close

Exit_btnClose_Click:
Exit Sub

Err_btnClose_Click:
MsgBox Err.Description
Resume Exit_btnClose_Click

End Sub

This is wizard generated code.

 

[Graham Mandeno]

Hi Rob

Does your code compile OK? (from the VBA window, click Debug>Compile)

I suspect that the problem is actually somewhere else in your code.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Robert

[snip]

Yes, that is exactly what I want and it is working perfectly except
for a new problem. I usually add my own buttons using the wizard for
navigation. Now I getting the following error msg:

The expression On Click you entered as the event property setting
produced the following
error: A problem occurred while Microsoft Office Access was
communicating with the OLE
server or ActiveX Control.
This error message occurs when I click on any of the buttons: Next,
Previous, Close ect. Any idea why is is happening?

My crystal ball is a bit cloudy ;-) It's very hard for me (or anyone) to
guess if you don't post the code that is raising the error.
--


Graham Mandeno [Access MVP]
Auckland, New Zealand

Understood the follows:


Private Sub btnFirst_Click()
On Error GoTo Err_btnFirst_Click


DoCmd.GoToRecord , , acFirst

Exit_btnFirst_Click:
Exit Sub

Err_btnFirst_Click:
MsgBox Err.Description
Resume Exit_btnFirst_Click

End Sub
Private Sub btnLast_Click()
On Error GoTo Err_btnLast_Click


DoCmd.GoToRecord , , acLast

Exit_btnLast_Click:
Exit Sub

Err_btnLast_Click:
MsgBox Err.Description
Resume Exit_btnLast_Click

End Sub
Private Sub btnNext_Click()
On Error GoTo Err_btnNext_Click


DoCmd.GoToRecord , , acNext

Exit_btnNext_Click:
Exit Sub

Err_btnNext_Click:
MsgBox Err.Description
Resume Exit_btnNext_Click

End Sub
Private Sub btnPrevious_Click()
On Error GoTo Err_btnPrevious_Click


DoCmd.GoToRecord , , acPrevious

Exit_btnPrevious_Click:
Exit Sub

Err_btnPrevious_Click:
MsgBox Err.Description
Resume Exit_btnPrevious_Click

End Sub
Private Sub btnClose_Click()
On Error GoTo Err_btnClose_Click


DoCmd.Close

Exit_btnClose_Click:
Exit Sub

Err_btnClose_Click:
MsgBox Err.Description
Resume Exit_btnClose_Click

End Sub

This is wizard generated code.

 

[KumbiaKid]

I'm getting exactly the same error message. I've simplified the OnClick code
to:

Private Sub CancelQueryCmdButton_Click()
MsgBox "TEST", vbOKOnly
Rem DoCmd.Close acForm, "SelectOneListNameForm"
End Sub

The Rem'd out code is what I really want, but it seems I can't execute any
code and I have no clue why.

KumbiaKid

Hi Robert

[snip]

Yes, that is exactly what I want and it is working perfectly except
for a new problem. I usually add my own buttons using the wizard for
navigation. Now I getting the following error msg:

The expression On Click you entered as the event property setting
produced the following
error: A problem occurred while Microsoft Office Access was
communicating with the OLE
server or ActiveX Control.

This error message occurs when I click on any of the buttons: Next,
Previous, Close ect. Any idea why is is happening?

My crystal ball is a bit cloudy ;-) It's very hard for me (or anyone) to
guess if you don't post the code that is raising the error.
--


Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi KumbiaKid

I ask you the same question I asked Rob: Does your code compile OK? (from
the VBA window, click Debug>Compile)

Often a syntax error in one part of your code will manifest itself by
causing a totally unrelated piece of code to fail at run-time.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm getting exactly the same error message. I've simplified the OnClick
code
to:

Private Sub CancelQueryCmdButton_Click()
MsgBox "TEST", vbOKOnly
Rem DoCmd.Close acForm, "SelectOneListNameForm"
End Sub

The Rem'd out code is what I really want, but it seems I can't execute any
code and I have no clue why.

KumbiaKid

Hi Robert

[snip]

Yes, that is exactly what I want and it is working perfectly except
for a new problem. I usually add my own buttons using the wizard for
navigation. Now I getting the following error msg:

The expression On Click you entered as the event property setting
produced the following
error: A problem occurred while Microsoft Office Access was
communicating with the OLE
server or ActiveX Control.

This error message occurs when I click on any of the buttons: Next,
Previous, Close ect. Any idea why is is happening?

My crystal ball is a bit cloudy ;-) It's very hard for me (or anyone) to
guess if you don't post the code that is raising the error.
--


Graham Mandeno [Access MVP]
Auckland, New Zealand