Sub Form on Navigation Form (1 Viewer)

[David_P]

I am having a problem with the subforms that I created with the Navigation Form. I am using Access 2010.

I have searched the web for the answer to my problem and can't figure out why my subforms are not working.

I created some forms and got them working the way I wanted. I then created a Navigation Form using the 'Create Navigation Form Button'. After I aded the forms and tried to run them, I get a Paramater Box asking for the paramaters for the subform I am trying to use.

My search of the web revealed that I need to reference the subform fromthe Main form by this format:

[Forms]![frmParentform]![frmNavigationsubform].[form].[cboControl]

So I put inserted the format into my VBA scripts following that format and got an Error 2465 'Can't find the field 'Transaction' reference to in you expression.' Here are the forms and control I put in the line:

frmParentform = Navigation Form
frmNavigationsubform = Transaction
cboControl = NewRecord

From what I understand, I have to refer to the subform control from the Main form. Am I missing something or left something out or just have the formating incorrect. Do I have to use this format trhoughout my VBA coding for the subforms?

Thanks

David

 

[Galaxiom]

The syntax should work but note the following.

Transaction and NewRecord are reserved words and should not be used for object names.

Don't use spaces in object names. Spaces require the use of the square brackets, making extra work and the code harder to read.

If the code is in the subform the reference to a control on that subform can be:
Me.controlname

 

[David_P]

I believe I have determine what is causing the problem with the subform when using it from the Navigation Form. The subform has a combobox that runs a query based on fields on the subform that the person inputs data into. When I click on the combobox to give me the list produced by the query it asks me for the paramaters for the fields fromt he sub forms what are:

[Forms]![Transactions]![dbo_Traceables_BarCode_Job]
[Forms]![Transactions]![dbo_Traceables_BarCode_PartNumber]
[Forms]![Transactions]![dbo_Checkpoints_BarCode_Task]
[Forms]![Transactions]![dbo_Checkpoints_BarCode_Employee]
[Forms]![Transactions]![dbo_Checkpoints_BarCode_Status]

The code for the query is as follows:

SELECT dbo_Transactions.ID, Left(dbo_Traceables.BarCode,7) AS Expr1001, 
Right(dbo_Traceables.BarCode,7) AS Expr1002, 
Left(dbo_Checkpoints.BarCode,5) AS Expr1003, 
Mid(dbo_Checkpoints.BarCode,6,5) AS Expr1004, 
Mid(dbo_Checkpoints.BarCode,11,3) AS Expr1005, 
Left(dbo_Checkpoints.Name,InStr(1,dbo_Checkpoints.Name,",")-1) AS Expr1006, 
Mid(dbo_Checkpoints.Name,InStr(1,dbo_Checkpoints.Name,",")+1,InStrRev(dbo_Checkpoints.Name,",",-1)-InStr(1,dbo_Checkpoints.Name,",")-1) AS Expr1007, 
Mid(dbo_Checkpoints.Name,InStrRev(dbo_Checkpoints.Name,",",-1)+1,4) AS Expr1008, 
Left(dbo_Transactions.Timestamp,InStr(1,dbo_Transactions.Timestamp," ")-1) AS Expr1009, 
Mid(dbo_Transactions.Timestamp,InStr(1,dbo_Transactions.Timestamp," ")+1,InStrRev(dbo_Transactions.Timestamp," ",-1)-3-InStr(1,dbo_Transactions.Timestamp," ")-1) AS Expr1010, 
Right(dbo_Transactions.Timestamp,2) AS Expr1011
FROM (dbo_Transactions INNER JOIN dbo_Traceables ON dbo_Transactions.TraceableID = dbo_Traceables.ID) INNER JOIN 
dbo_Checkpoints ON dbo_Transactions.CheckpointID = dbo_Checkpoints.ID
WHERE (((Left([dbo_Traceables].[BarCode],7))=[Forms]![Transactions]![dbo_Traceables_BarCode_Job]) AND 
((Right([dbo_Traceables].[BarCode],7))=[Forms]![Transactions]![dbo_Traceables_BarCode_PartNumber]) AND 
((Left([dbo_Checkpoints].[BarCode],5))=[Forms]![Transactions]![dbo_Checkpoints_BarCode_Task]) AND 
((Mid([dbo_Checkpoints].[BarCode],6,5))=[Forms]![Transactions]![dbo_Checkpoints_BarCode_Employee]) AND 
((Mid([dbo_Checkpoints].[BarCode],11,3))=[Forms]![Transactions]![dbo_Checkpoints_BarCode_Status]))
ORDER BY dbo_Transactions.ID DESC;

What do I need to do to get the query to obtain the data from the fields from the subform for the query?

Thanks.

 

[boblarson]

You are missing the reference to the parent form (and make sure that the subform name you are using here is the name of the CONTROL on the main form which HOUSES the subform - not the subform name itself unless it and the subform share the EXACT same name):

WHERE (((Left([dbo_Traceables].[BarCode],7))=[Forms][B][COLOR=red]![Navigation Form][/COLOR][/B]![Transactions]![dbo_Traceables_BarCode_Job]) AND 
((Right([dbo_Traceables].[BarCode],7))=[Forms][B][COLOR=red]![Navigation Form][/COLOR][/B]![Transactions]![dbo_Traceables_BarCode_PartNumber]) AND 
((Left([dbo_Checkpoints].[BarCode],5))=[Forms][B][COLOR=red]![Navigation Form][/COLOR][/B]![Transactions]![dbo_Checkpoints_BarCode_Task]) AND 
((Mid([dbo_Checkpoints].[BarCode],6,5))=[Forms][B][COLOR=red]![Navigation Form][/COLOR][/B]![Transactions]![dbo_Checkpoints_BarCode_Employee]) AND 
((Mid([dbo_Checkpoints].[BarCode],11,3))=[Forms][COLOR=red][B]![Navigation Form][/B][/COLOR]![Transactions]![dbo_Checkpoints_BarCode_Status]))
ORDER BY dbo_Transactions.ID DESC;

 

[David_P]

I added in ![Navigation Form] into the script but it still comes up with the parameter boxes.

I am not sure what you mean by "and make sure that the subform name you are using here is the name of the CONTROL on the main form which HOUSES the subform - not the subform name itself unless it and the subform share the EXACT same name".

I created the Navigation Form using the Create Navigation button in Access 2010 then added the subform to the form by adding a new Navigation button in the NavigationControl area. If that makes any sense.

 

[boblarson]

I added in ![Navigation Form] into the script but it still comes up with the parameter boxes.

Then you are either referring to the wrong object or have something spelled incorrectly.

I am not sure what you mean by "and make sure that the subform name you are using here is the name of the CONTROL on the main form which HOUSES the subform - not the subform name itself unless it and the subform share the EXACT same name".

Read my short tutorial here and pay attention to the screen shot which shows you what I'm talking about.

 

[David_P]

Well, it seems like I figure out the problem and solution. And of course I just made the problem more complex then it really was.
All I had to do was remove [Forms]![Navigation_Form]![Transactions]! and the extra brackets from the code for the Where section of the SQL and it worked.
Old Code:

WHERE (((Left([dbo_Traceables].[BarCode],7))=[Forms]![Transactions]![dbo_Traceables_BarCode_Job]) AND 
((Right([dbo_Traceables].[BarCode],7))=[Forms]![Transactions]![dbo_Traceables_BarCode_PartNumber]) AND 
((Left([dbo_Checkpoints].[BarCode],5))=[Forms]![Transactions]![dbo_Checkpoints_BarCode_Task]) AND 
((Mid([dbo_Checkpoints].[BarCode],6,5))=[Forms]![Transactions]![dbo_Checkpoints_BarCode_Employee]) AND 
((Mid([dbo_Checkpoints].[BarCode],11,3))=[Forms]![Transactions]![dbo_Checkpoints_BarCode_Status]))

NewCode:

WHERE (((Left([dbo_Traceables].[BarCode],7))=dbo_Traceables_BarCode_Job) AND 
((Right([dbo_Traceables].[BarCode],7))=dbo_Traceables_BarCode_PartNumber) AND 
((Left([dbo_Checkpoints].[BarCode],5))=dbo_Checkpoints_BarCode_Task) AND 
((Mid([dbo_Checkpoints].[BarCode],6,5))=dbo_Checkpoints_BarCode_Employee) AND 
((Mid([dbo_Checkpoints].[BarCode],11,3))=dbo_Checkpoints_BarCode_Status))

Thanks for you all your help.