Need to use F9 to requery in subform

B

Brian Fielding

The data base consists of 2 key tables Scanner Models and Scanner Parts. As
this is a many-to-many relationaship and there is a linking table ModelPart.

When work is carried out on a Scanner a Service Report is input. The Service
Report consists of a main form (recording dates, work done etc, Scanner
Model) and a subform (to enter details of parts used). The Service Report
number is the Master Link Field and Child Link Field.

On entereing a Service Report the Scanner Model is selected from a combo
box. On entering the subform and clicking on the PartID combo box a list of
parts based on the Scanner Model (created using a query on the Row Source)
enables Parts used to be selected and quantities etc to be added.

My problem is: when you add a new Service Report and enter the relevant
fields and then enter the Parts subform the Parts are displayed correctly
but . . . if you now add a second Service Report and after entering the
relevant fields you enter the subform the Parts combo box does not display
any parts (just headings). However if you press F9 and then re-click on the
Parts combo box the correct list of parts is shown.

I need to add a requery somewhere (I have tried several events) - what
requery is needed and whch event do I add it to ?

Thanks
Brian
 
A

Al Campagna

Brian,
When one combo relies on the value/s of another combo/s, use the
After Update event of the preceding combos to Requery.
Sounds like the AfterUpdate event of your Model combo on the main
would be the event to initiate your subform combo Requery.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
B

Brian Fielding

Not sure what format the requery should be:

I tried (as well as others):
Forms!frmServiceReport.frmServicePart.Form.Requery

I get Run-time error '2465' Application-defined or object-defined error.

Thanks for your help
Brian
 
B

Brian Fielding

Hi Al

Sorry - now get run-time error 2465 Reprotec Maintenance Database can't
find the field 'frmServicePart' referred to in your expression.

I've appended the frmServiceReport and frmServicePart code at the end in
case this might help

Thanks Again
Brian

Al Campagna said:
Brian,
Try...
Forms!frmServiceReport!frmServicePart.Form!YourComboName.Requery
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
Click to expand...


frmServiceReport

Option Compare Database
Option Explicit

Private Sub Close_service_report_Click()
On Error GoTo Err_Close_service_report_Click

DoCmd.Close

Exit_Close_service_report_Click:
Exit Sub

Err_Close_service_report_Click:
MsgBox Err.Description
Resume Exit_Close_service_report_Click

End Sub

Private Sub CustomerID_AfterUpdate()

Me![ScanOwner] = CustomerID.Column(0)

End Sub

Private Sub New_service_report_Click()
On Error GoTo Err_New_service_report_Click

DoCmd.GoToRecord , , acNewRec

Exit_New_service_report_Click:
Exit Sub

Err_New_service_report_Click:
MsgBox Err.Description
Resume Exit_New_service_report_Click

End Sub

Private Sub delete_service_report_Click()
On Error GoTo Err_delete_service_report_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_delete_service_report_Click:
Exit Sub

Err_delete_service_report_Click:
MsgBox Err.Description
Resume Exit_delete_service_report_Click

End Sub

Private Sub Find_Service_report_Click()
On Error GoTo Err_Find_Service_report_Click

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Service_report_Click:
Exit Sub

Err_Find_Service_report_Click:
MsgBox Err.Description
Resume Exit_Find_Service_report_Click

End Sub



Private Sub ScanMake_AfterUpdate()

Me!ScanModel = ScanMake.Column(1)

'Note: ScanModel is the link to the subform. It is determined from the
ScanMake using the table refScannerMakes

Forms!frmServiceReport!frmServicePart.Form!PartID.Requery

End Sub




frmServicePart

Option Compare Database
Option Explicit

Private Sub Open_Parts_Price_List_Click()
On Error GoTo Err_Open_Parts_Price_List_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Parts Price list"

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

Exit_Open_Parts_Price_List_Click:
Exit Sub

Err_Open_Parts_Price_List_Click:
MsgBox Err.Description
Resume Exit_Open_Parts_Price_List_Click

End Sub

Private Sub Form_Current()

Me.Form.PartID.Requery

End Sub

Private Sub PartID_AfterUpdate()

'Note this is the problem area that F9 resolves

Me!PartName = PartID.Column(1)
Me!PartCost = PartID.Column(2)
If Me!PartQty < 1 Then Me!PartQty = 1

End Sub
 
A

Al Campagna

Brian,
Whenever you have problems with code, cut & paste the code you
used exactly as you have it, into your reply.
I got the name frmServicePart from your code example...

You wrote...In any code suggested, you have to make sure you own object names
in place of any the responder might refer to...
What is your main form name, subform name, combo name?
I've appended the frmServiceReport and frmServicePart code at the end in
case this might help
Click to expand...
I don't know what that means...

Forms!frmMainFormName!frmSubFormName.Form!cboYourComboName.Requery
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Brian Fielding said:
Hi Al

Sorry - now get run-time error 2465 Reprotec Maintenance Database can't
find the field 'frmServicePart' referred to in your expression.

I've appended the frmServiceReport and frmServicePart code at the end in
case this might help

Thanks Again
Brian

Al Campagna said:
Brian,
Try...
Forms!frmServiceReport!frmServicePart.Form!YourComboName.Requery
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."
Click to expand...


frmServiceReport

Option Compare Database
Option Explicit

Private Sub Close_service_report_Click()
On Error GoTo Err_Close_service_report_Click

DoCmd.Close

Exit_Close_service_report_Click:
Exit Sub

Err_Close_service_report_Click:
MsgBox Err.Description
Resume Exit_Close_service_report_Click

End Sub

Private Sub CustomerID_AfterUpdate()

Me![ScanOwner] = CustomerID.Column(0)

End Sub

Private Sub New_service_report_Click()
On Error GoTo Err_New_service_report_Click

DoCmd.GoToRecord , , acNewRec

Exit_New_service_report_Click:
Exit Sub

Err_New_service_report_Click:
MsgBox Err.Description
Resume Exit_New_service_report_Click

End Sub

Private Sub delete_service_report_Click()
On Error GoTo Err_delete_service_report_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_delete_service_report_Click:
Exit Sub

Err_delete_service_report_Click:
MsgBox Err.Description
Resume Exit_delete_service_report_Click

End Sub

Private Sub Find_Service_report_Click()
On Error GoTo Err_Find_Service_report_Click

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Service_report_Click:
Exit Sub

Err_Find_Service_report_Click:
MsgBox Err.Description
Resume Exit_Find_Service_report_Click

End Sub



Private Sub ScanMake_AfterUpdate()

Me!ScanModel = ScanMake.Column(1)

'Note: ScanModel is the link to the subform. It is determined from the
ScanMake using the table refScannerMakes

Forms!frmServiceReport!frmServicePart.Form!PartID.Requery

End Sub




frmServicePart

Option Compare Database
Option Explicit

Private Sub Open_Parts_Price_List_Click()
On Error GoTo Err_Open_Parts_Price_List_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Parts Price list"

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

Exit_Open_Parts_Price_List_Click:
Exit Sub

Err_Open_Parts_Price_List_Click:
MsgBox Err.Description
Resume Exit_Open_Parts_Price_List_Click

End Sub

Private Sub Form_Current()

Me.Form.PartID.Requery

End Sub

Private Sub PartID_AfterUpdate()

'Note this is the problem area that F9 resolves

Me!PartName = PartID.Column(1)
Me!PartCost = PartID.Column(2)
If Me!PartQty < 1 Then Me!PartQty = 1

End Sub
Click to expand...
 
B

Brian Fielding

Al

After much seaching and experimentation I now understand more about what is
happening;

Main Form: frmServiceReport
Subform: frmServicePart however the control name is [Service Part
Subform] or Service_Part_Subform
combo name (in main form): ScanMake
combo name (in subform): PartID

The code for the AfterUpdate event for ScanModel is now and it appears to
work OK.

Private Sub ScanMake_AfterUpdate()

Me!ScanModel = ScanMake.Column(1)
Forms!frmServiceReport![Service Part Subform].Form!PartID.Requery

End Sub


I'm sorry if I mislead you on the form name. It is early days for me on
Access - give me Algol, Fortran or Pascal any day.

Thanks very much for your help
Brian



Al Campagna said:
Brian,
Whenever you have problems with code, cut & paste the code you
used exactly as you have it, into your reply.
I got the name frmServicePart from your code example...

You wrote...In any code suggested, you have to make sure you own object names
in place of any the responder might refer to...
What is your main form name, subform name, combo name?
I've appended the frmServiceReport and frmServicePart code at the end in
case this might help
Click to expand...
I don't know what that means...

Forms!frmMainFormName!frmSubFormName.Form!cboYourComboName.Requery
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Brian Fielding said:
Hi Al

Sorry - now get run-time error 2465 Reprotec Maintenance Database can't
find the field 'frmServicePart' referred to in your expression.

I've appended the frmServiceReport and frmServicePart code at the end in
case this might help

Thanks Again
Brian

Al Campagna said:
Brian,
Try...
Forms!frmServiceReport!frmServicePart.Form!YourComboName.Requery
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Not sure what format the requery should be:

I tried (as well as others):
Forms!frmServiceReport.frmServicePart.Form.Requery

I get Run-time error '2465' Application-defined or object-defined
error.

Thanks for your help
Brian
Brian,
When one combo relies on the value/s of another combo/s, use the
After Update event of the preceding combos to Requery.
Sounds like the AfterUpdate event of your Model combo on the main
would be the event to initiate your subform combo Requery.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."



The data base consists of 2 key tables Scanner Models and Scanner
Parts. As this is a many-to-many relationaship and there is a linking
table ModelPart.

When work is carried out on a Scanner a Service Report is input. The
Service Report consists of a main form (recording dates, work done
etc, Scanner Model) and a subform (to enter details of parts used).
The Service Report number is the Master Link Field and Child Link
Field.

On entereing a Service Report the Scanner Model is selected from a
combo box. On entering the subform and clicking on the PartID combo
box a list of parts based on the Scanner Model (created using a query
on the Row Source) enables Parts used to be selected and quantities
etc to be added.

My problem is: when you add a new Service Report and enter the
relevant fields and then enter the Parts subform the Parts are
displayed correctly but . . . if you now add a second Service Report
and after entering the relevant fields you enter the subform the
Parts combo box does not display any parts (just headings). However
if you press F9 and then re-click on the Parts combo box the correct
list of parts is shown.

I need to add a requery somewhere (I have tried several events) -
what requery is needed and whch event do I add it to ?

Thanks
Brian
Click to expand...


frmServiceReport

Option Compare Database
Option Explicit

Private Sub Close_service_report_Click()
On Error GoTo Err_Close_service_report_Click

DoCmd.Close

Exit_Close_service_report_Click:
Exit Sub

Err_Close_service_report_Click:
MsgBox Err.Description
Resume Exit_Close_service_report_Click

End Sub

Private Sub CustomerID_AfterUpdate()

Me![ScanOwner] = CustomerID.Column(0)

End Sub

Private Sub New_service_report_Click()
On Error GoTo Err_New_service_report_Click

DoCmd.GoToRecord , , acNewRec

Exit_New_service_report_Click:
Exit Sub

Err_New_service_report_Click:
MsgBox Err.Description
Resume Exit_New_service_report_Click

End Sub

Private Sub delete_service_report_Click()
On Error GoTo Err_delete_service_report_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_delete_service_report_Click:
Exit Sub

Err_delete_service_report_Click:
MsgBox Err.Description
Resume Exit_delete_service_report_Click

End Sub

Private Sub Find_Service_report_Click()
On Error GoTo Err_Find_Service_report_Click

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Service_report_Click:
Exit Sub

Err_Find_Service_report_Click:
MsgBox Err.Description
Resume Exit_Find_Service_report_Click

End Sub



Private Sub ScanMake_AfterUpdate()

Me!ScanModel = ScanMake.Column(1)

'Note: ScanModel is the link to the subform. It is determined from the
ScanMake using the table refScannerMakes

Forms!frmServiceReport!frmServicePart.Form!PartID.Requery

End Sub




frmServicePart

Option Compare Database
Option Explicit

Private Sub Open_Parts_Price_List_Click()
On Error GoTo Err_Open_Parts_Price_List_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Parts Price list"

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

Exit_Open_Parts_Price_List_Click:
Exit Sub

Err_Open_Parts_Price_List_Click:
MsgBox Err.Description
Resume Exit_Open_Parts_Price_List_Click

End Sub

Private Sub Form_Current()

Me.Form.PartID.Requery

End Sub

Private Sub PartID_AfterUpdate()

'Note this is the problem area that F9 resolves

Me!PartName = PartID.Column(1)
Me!PartCost = PartID.Column(2)
If Me!PartQty < 1 Then Me!PartQty = 1

End Sub
Click to expand...
Click to expand...
 
A

Al Campagna

Brian,
Good deal! Always use your own object "Name" property.
And... no problem on the mix up. That's what we're here for...
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Brian Fielding said:
Al

After much seaching and experimentation I now understand more about what
is happening;

Main Form: frmServiceReport
Subform: frmServicePart however the control name is [Service Part
Subform] or Service_Part_Subform
combo name (in main form): ScanMake
combo name (in subform): PartID

The code for the AfterUpdate event for ScanModel is now and it appears to
work OK.

Private Sub ScanMake_AfterUpdate()

Me!ScanModel = ScanMake.Column(1)
Forms!frmServiceReport![Service Part Subform].Form!PartID.Requery

End Sub


I'm sorry if I mislead you on the form name. It is early days for me on
Access - give me Algol, Fortran or Pascal any day.

Thanks very much for your help
Brian



Al Campagna said:
Brian,
Whenever you have problems with code, cut & paste the code you
used exactly as you have it, into your reply.
I got the name frmServicePart from your code example...

You wrote...
Forms!frmServiceReport.frmServicePart.Form.Requery
Click to expand...
In any code suggested, you have to make sure you own object names
in place of any the responder might refer to...
What is your main form name, subform name, combo name?
I've appended the frmServiceReport and frmServicePart code at the end in
case this might help
Click to expand...
I don't know what that means...

Forms!frmMainFormName!frmSubFormName.Form!cboYourComboName.Requery
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Brian Fielding said:
Hi Al

Sorry - now get run-time error 2465 Reprotec Maintenance Database can't
find the field 'frmServicePart' referred to in your expression.

I've appended the frmServiceReport and frmServicePart code at the end in
case this might help

Thanks Again
Brian

Brian,
Try...
Forms!frmServiceReport!frmServicePart.Form!YourComboName.Requery
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Not sure what format the requery should be:

I tried (as well as others):
Forms!frmServiceReport.frmServicePart.Form.Requery

I get Run-time error '2465' Application-defined or object-defined
error.

Thanks for your help
Brian
Brian,
When one combo relies on the value/s of another combo/s, use the
After Update event of the preceding combos to Requery.
Sounds like the AfterUpdate event of your Model combo on the main
would be the event to initiate your subform combo Requery.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."



The data base consists of 2 key tables Scanner Models and Scanner
Parts. As this is a many-to-many relationaship and there is a
linking
table ModelPart.

When work is carried out on a Scanner a Service Report is input. The
Service Report consists of a main form (recording dates, work done
etc, Scanner Model) and a subform (to enter details of parts used).
The Service Report number is the Master Link Field and Child Link
Field.

On entereing a Service Report the Scanner Model is selected from a
combo box. On entering the subform and clicking on the PartID combo
box a list of parts based on the Scanner Model (created using a
query
on the Row Source) enables Parts used to be selected and quantities
etc to be added.

My problem is: when you add a new Service Report and enter the
relevant fields and then enter the Parts subform the Parts are
displayed correctly but . . . if you now add a second Service Report
and after entering the relevant fields you enter the subform the
Parts combo box does not display any parts (just headings). However
if you press F9 and then re-click on the Parts combo box the correct
list of parts is shown.

I need to add a requery somewhere (I have tried several events) -
what requery is needed and whch event do I add it to ?

Thanks
Brian



frmServiceReport

Option Compare Database
Option Explicit

Private Sub Close_service_report_Click()
On Error GoTo Err_Close_service_report_Click

DoCmd.Close

Exit_Close_service_report_Click:
Exit Sub

Err_Close_service_report_Click:
MsgBox Err.Description
Resume Exit_Close_service_report_Click

End Sub

Private Sub CustomerID_AfterUpdate()

Me![ScanOwner] = CustomerID.Column(0)

End Sub

Private Sub New_service_report_Click()
On Error GoTo Err_New_service_report_Click

DoCmd.GoToRecord , , acNewRec

Exit_New_service_report_Click:
Exit Sub

Err_New_service_report_Click:
MsgBox Err.Description
Resume Exit_New_service_report_Click

End Sub

Private Sub delete_service_report_Click()
On Error GoTo Err_delete_service_report_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_delete_service_report_Click:
Exit Sub

Err_delete_service_report_Click:
MsgBox Err.Description
Resume Exit_delete_service_report_Click

End Sub

Private Sub Find_Service_report_Click()
On Error GoTo Err_Find_Service_report_Click

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Service_report_Click:
Exit Sub

Err_Find_Service_report_Click:
MsgBox Err.Description
Resume Exit_Find_Service_report_Click

End Sub



Private Sub ScanMake_AfterUpdate()

Me!ScanModel = ScanMake.Column(1)

'Note: ScanModel is the link to the subform. It is determined from the
ScanMake using the table refScannerMakes

Forms!frmServiceReport!frmServicePart.Form!PartID.Requery

End Sub




frmServicePart

Option Compare Database
Option Explicit

Private Sub Open_Parts_Price_List_Click()
On Error GoTo Err_Open_Parts_Price_List_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Parts Price list"

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

Exit_Open_Parts_Price_List_Click:
Exit Sub

Err_Open_Parts_Price_List_Click:
MsgBox Err.Description
Resume Exit_Open_Parts_Price_List_Click

End Sub

Private Sub Form_Current()

Me.Form.PartID.Requery

End Sub

Private Sub PartID_AfterUpdate()

'Note this is the problem area that F9 resolves

Me!PartName = PartID.Column(1)
Me!PartCost = PartID.Column(2)
If Me!PartQty < 1 Then Me!PartQty = 1

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Wrong data is presented after query 1
Part list not updated on change of type. 2
Blank third cascading combo... STUCK! 3
How make subform independent of main form? 11
Requery combo box in subform 5
requery continuour subform 11
requery subform combo afterupdate of main form combo 5
Pass Subform filter to subform in report 0

Top