Need Help with a control button (1 Viewer)

[cstacy]

Hi I hope you all can help me.

I have created a form with a subform for training. The database works great and I'm close to finishing it. One of the issues I have now come across is printing a single record from the form. The VBA I use is:

Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[ID] = " & Me.[Part Number]
DoCmd.OpenReport "Skills Matrix", acViewPreview, , strWhere
End If

This works great, but will only give me the first entry off of the subform, when I want all of the entry's on the current subform.

At the moment I can either get the first entry from the subform or all the entry's for all the items. I want it to print for the current item with all people listed on the current subform.

Any help would be great. I've attached the file to look at as well. The form with the command button is the Operator Skills form.

Thank you again.

 

[RuralGuy]

I'll start by saying that once Me.Dirty = False has been executed, Me.NewRecord will *always* be false.

 

[MStef]

I think you need something like "DemoRptSubFA2000.mdb" (attachment, zip).
Look at tables, forms, queries, reports.
Open "frmMAIN" and try.

 

[cstacy]

I think you need something like "DemoRptSubFA2000.mdb" (attachment, zip).
Look at tables, forms, queries, reports.
Open "frmMAIN" and try.

Sorry I spoke too soon.

On Error GoTo Err_cmdPrint_Click
DoCmd.OpenReport "Skills Matrix", acPreview
Exit_cmdPrint_Click:
Exit Sub
Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

This gives me the report, but also for all of the records. I'm not sure how to limit it to just the active form using this.

Thanks,

 

[RuralGuy]

The same way you would open a Form to a particular record. http://www.baldyweb.com/wherecondition.htm

 

[cstacy]

The same way you would open a Form to a particular record. http://www.baldyweb.com/wherecondition.htm

Thank you for the link, but I can't decifer that to save my life. I am very much a novice at Access, and while I have most of the database the way it needs to be this one thing is really stopping me.

With most of the VBA I have found it either gives me all of the records or limits it all the way down to only one subform record.

The database that MStef shows exactly what I want mine to do, but when I paste the VBA into mine I get all the records again.

Thanks again.

 

[RuralGuy]

You use the 4th argument of the OpenReport command. It is the WhereCondition argument.

 

[vbaInet]

I think cstacy is already using the 4th argument.

@cstacy: What is the field that links the subform to the main form? You need to include that in your criteria (i.e. the WHERE part of the OpenReport command). Also, if you applied any filter on the form or the subform, they also need to be included in the criteria of the OpenReport command.

 

[RuralGuy]

...and if the [ID] field is a string instead of a number then you will need this:
strWhere = "[ID] = '" & Me.[Part Number] & "'"

 

[cstacy]

I think cstacy is already using the 4th argument.

@cstacy: What is the field that links the subform to the main form? You need to include that in your criteria (i.e. the WHERE part of the OpenReport command). Also, if you applied any filter on the form or the subform, they also need to be included in the criteria of the OpenReport command.

Thank you for all of your help.

The main form uses the Part Number field as the index which relates to the Part Number field on the subform though the Subform has an index called ID.

Part number is a text field, and ID is an autonumber.

Thank you again.

 

[vbaInet]

I've just had a look at your db. Although I couldn't run it because I don't have Access 2010, I was able to look at your tables and report. So the code should be:

strWhere = "[tblParts_Part Number] = " & Me.[Part Number]

Either tblParts_Part Number or Operator Skill_Part number.

Also, your db seriously needs to be normalized. Here's more info:

http://support.microsoft.com/kb/283878
http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

 

[cstacy]

I've just had a look at your db. Although I couldn't run it because I don't have Access 2010, I was able to look at your tables and report. So the code should be:

strWhere = "[tblParts_Part Number] = " & Me.[Part Number]

Either tblParts_Part Number or Operator Skill_Part number.

Also, your db seriously needs to be normalized. Here's more info:

http://support.microsoft.com/kb/283878
http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Thank you for the code, yet I still get a data type mismatch is criteria expression error which it then points to:

DoCmd.OpenReport "Skills Matrix", acViewPreview, , strWhere

As for normalizing I'm absolutely horrible at it. Once I get this expression sorted I'll see what I can do.

Thanks for your help again.

 

[RuralGuy]

If Part Number is a string (which it should be) as you say then the code should be:
strWhere = "[tblParts_Part Number] = '" & Me.[Part Number] & "'"

 

[cstacy]

If Part Number is a string (which it should be) as you say then the code should be:
strWhere = "[tblParts_Part Number] = '" & Me.[Part Number] & "'"

Just added in the extra quotations and it works great! Thank you to everyone who helped.

It only shows you how worthless I am in access. I should stay in excel, haha.

Thank you so much.

 

[RuralGuy]

Excellent! Thanks for posting back with your success.