×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Command Button not opening form to show linked data.

Command Button not opening form to show linked data.

Command Button not opening form to show linked data.

(OP)
I have a command button on a 'Inspection' form
This command button is to open an additional form that shows the inspection details.
When clicking on the command button, the form opens to a blank form and not showing any data.

Any idea why this is not working? This is the code I have behind the button:

Private Sub Command82_Click()
On Error GoTo Err_Command82_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FDA-Form"

stLinkCriteria = "[InspID]=" & Me![InspectionID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command82_Click:
Exit Sub

Err_Command82_Click:
MsgBox Err.Description
Resume Exit_Command82_Click

End Sub

RE: Command Button not opening form to show linked data.

Please use the TGML code tag so your code is more readable.

I would make sure the value of InspectionID is what you think it should be. Can we assume InspID is numeric?

CODE --> vba

Private Sub Command82_Click()
    On Error GoTo Err_Command82_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FDA-Form"

    stLinkCriteria = "[InspID]=" & Me![InspectionID]
    MsgBox "Here is the criteria: " & "[InspID]=" & Me![InspectionID]

    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command82_Click:
    Exit Sub

Err_Command82_Click:
    MsgBox Err.Description
    Resume Exit_Command82_Click

End Sub 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Command Button not opening form to show linked data.

(OP)
Duane, thank you for your help:
Yes, the value is numeric.
I ran the code you provided and the message box did bring up the correct inspID, but still only brings up a blank form (with no inspID) instead of the inspection that is linked to the inspID

RE: Command Button not opening form to show linked data.

What is the forms record source?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Command Button not opening form to show linked data.

monkeysee, do you really have 82 (or more?) command buttons on your Form? ponder


---- Andy

There is a great need for a sarcasm font.

RE: Command Button not opening form to show linked data.

(OP)
dhookom

The record source for the host form which holds the button is a Query:


The record source for the form which the button is suppose to open is a table:

RE: Command Button not opening form to show linked data.

Your criteria sets InspID field, but your FieldName is InspD (with missing I)


---- Andy

There is a great need for a sarcasm font.

RE: Command Button not opening form to show linked data.

Good catch Andy!

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Command Button not opening form to show linked data.

I would hope that this will error out, but monkeysee never mentioned any errors...


---- Andy

There is a great need for a sarcasm font.

RE: Command Button not opening form to show linked data.

(OP)
dhookom, & Andrzejek

Thank you both, but...

I was really hoping that the misspelling was the problem, but it wasn't. I corrected and still pulling a blank form. No error messages, just a blank form. I also changed the properties of the form to allow data entry and vice a versa, neither had any affect.

RE: Command Button not opening form to show linked data.

Again, please provide the SQL view of your forms record source. No pictures.

What do you see if you open the form from the navigation pane?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Command Button not opening form to show linked data.

(OP)
Opening the form from the navigation pane, shows the records that have been entered so data is being saved.

Here is what is weird: I have a backup copy of the dB before it was split. The code works in the 'whole' dB, shows the records when clicking the button. But in the split dB, 'fe' where the forms, etc are, it does not work. Don't understand why splitting the dB would interfere with the same code.

This is the SQL view of the record source from the 'host' form from the split dB

CODE -->

SELECT Inspections.InspectionID, Food.FacilityID, Food.BusinessName, Inspections.InspectionDate, Inspections.FacilityID, Inspections.TypeID, Food.Priority, Food.NameID, Food.FacilityPhone, Food.AddressLocationID, Food.[Fax#], Inspections.InspectorID, Inspections.NoFoodPrep, Inspections.DateRecorded, Inspections.CriticalFollowUpRequired, Inspections.CriticalFollowUpDueDate, Inspections.CriticalFollowUpSatisfactory, Inspections.NonCriticalDueDate, Inspections.NonCriticalFormReceivedDate, Inspections.NonCriticalSatisfactory, Inspections.[1-Steps of HACCP], Inspections.[2-PHF], Inspections.[8-Risk_Assmnt], Inspections.[9-TimeTemp], Inspections.[10-PestControl], Inspections.[7-Cooking/CoolingProc], Inspections.[3-Handwashing], Inspections.[4-Reheating], Inspections.[6-CrossCont], Inspections.[5-MonitoryCriticalPoints], Inspections.TimeIn, Inspections.TimeOut, Inspections.WaterPublic, Inspections.WaterPrivate, Inspections.Sanitizer, Inspections.PPM, Inspections.Heat, Inspections.PersonID, Inspections.WastePublic, Inspections.WastePrivate
FROM Food LEFT JOIN Inspections ON Food.FacilityID = Inspections.FacilityID
ORDER BY Inspections.InspectionDate DESC , Inspections.InspectionDate DESC; 

This is the code behind the button:

CODE -->

On Error GoTo Err_Command74_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FDA-Form"
    
    stLinkCriteria = "[InspID]=" & Me![InspectionID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command74_Click:
    Exit Sub

Err_Command74_Click:
    MsgBox Err.Description
    Resume Exit_Command74_Click 

Record source for form the button is to open is a table. (I converted it to query so you can see the sql)

CODE -->

SELECT [FDA-insp].FDAID, [FDA-insp].InspID, [FDA-insp].[1Person charge present,demonstrates knowledge & performs duties], [FDA-insp].[2Certified Food Protection Manager CFPM], [FDA-insp].[3Mgmt, Food Emp & cond emp; knowledge, responsibilities & report]
FROM [FDA-insp]; 

Thanks for you time and help! It is much appreciated!

RE: Command Button not opening form to show linked data.

I would place a text box on the form the button is to open and set its Control SOurce to
=[Filter]

This should display something like
[InspID]=13

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Command Button not opening form to show linked data.

(OP)
Duane,

Hope you had a great weekend!

I tried the text box as you suggested, but that didn't work either, the text box returns the correct inspID, but the data didn't come with it, just a form with no data entered
I decided to import the form from the pre-split DB and that worked. So the form in the split must have been corrupt.

But now I have another question. Same host forms, queries, field names etc.
The host form (frmInsp) has the preliminary information of the inspection; ie: date, inspector name and assigns an insp# (which uses the inspectionID.)
The second form: FDA-Insp-New-Form - allows data entry for the inspection findings.
Right now when clicking on the command button to open the 2nd form, the user must enter the inspection # in order to link the data in the 2 forms.
I would like the 2nd form to open with the InspID filled in, so that the user does not have to enter it and eliminate any error with the inspID.
I'm using the same code (except with a different stDocName

Is there a way to pass the InspectionID from the first form to the second form. The second form has the InspID field which is the linking field to the first form.

Thanks for all your help!

RE: Command Button not opening form to show linked data.

Most of us would simply use a sub form for entering the child records. No code, no fuss.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Command Button not opening form to show linked data.

(OP)
As would I, but this particular form, the inspection findings, has 90 fields in it by itself which is the first page. And then there is a subform for page 2 with another 90 fields. So isn't too large for a subform?

RE: Command Button not opening form to show linked data.

IMO, your table structure is horribly wrong since you are storing data in field names and not in field values.

"1Person charge present,demonstrates knowledge & performs duties", "2Certified Food Protection Manager CFPM", "3Mgmt, Food Emp & cond emp; knowledge, responsibilities & report" are all values that should be stored as values in a table.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Command Button not opening form to show linked data.

(OP)
I am open for suggestions. The reason I did it this way was to insure that all fields print out on the final report whether there is a finding or not. And to make it easy and time efficient for the inspector out in the field to enter info through their tablet. I'm attaching a copy of the what the end result (report) must look like which is Requirement of the FDA and State Regs.[img ][img ]

RE: Command Button not opening form to show linked data.

I would have a table with the main inspection including only fields from the top. I would then set up one table 58 Inspection Points with the number [InspPointID] as well as the category and subcategory. This suggests extra to store the two categories and the subcategories (shaded background in image). Every new inspection would append 58 records to a child table that also contains the inspection id and [InspPointID].

I never let a finished report design/layout determine my table structure.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Command Button not opening form to show linked data.

(OP)
How would you address the 'In", "out" "N/A", N/O" grading of each point?
Now, I have it as a look-up table for each point with a default of "IN"
And the COS and R for each point, I now have check boxes for each
With the look-up fields and the check boxes, adds three additional fields for each point.
Is there a better way to do that?

RE: Command Button not opening form to show linked data.

I would maintain a small lookup table of potential values. The COS and R could be a field in the Inspection Point table. You would basically be replacing fields in you existing table with records in a related table. This is "normalization".

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Command Button not opening form to show linked data.

(OP)
Duane,
I really want to tell you how much I really appreciate your help with this. I have been working on what you have suggested. This is what I have so far.
CategoryTable with fields CategoryID and Title
SubCatTable, with SubCatID, Description and Category ID
FDA-PointTable, with FDApointID, Description (which will hold all 58points) but I only added 10 for now to see how everything works first.
My Original InspectionTable with InspID and has all the fields needed for the top of the report.

Am I on the right track? I tried creating a subform with the INSPID link between InspectionTable and FDA-pointTable, but the child form doesn't display any field from the FDA-PointTable. If I remove the InspID, then it does display them, but that defeats the purpose of linking the info with the host form.

You mentioned a child table with inspectionID and inspPointID. Are you referring to a junction table with both having the primary key? And then using that as a "middle man" so to speak?

Thank you again!

RE: Command Button not opening form to show linked data.

Quote (monkeysee)

You mentioned a child table with inspectionID and inspPointID. Are you referring to a junction table with both having the primary key? And then using that as a "middle man" so to speak?
Yes, the junction table is where you would enter all of your inspection point results. You could create an append query to run from your main inspection form that would be based on the [FDA-PointTable] and the current InspectionID.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Command Button not opening form to show linked data.

(OP)
Duane,
I think it is starting to come together. I now have a subform based on a query:

CODE -->

SELECT QryFDAjunction.FDApointID, FDApt.Description, QryFDAjunction.Cos, QryFDAjunction.R, QryFDAjunction.NonCompliance, QryFDAjunction.InspectionID
FROM QryFDAjunction RIGHT JOIN FDApt ON QryFDAjunction.FDApointID = FDApt.FDAPtID
WITH OWNERACCESS OPTION; 

When form opens, no items from the 'point table' are listed. but when entering the item # in the ID column, the description pops up. This appears that this would be the case for each of the 58 points. Is this how it is to work?

Thanks Again!

RE: Command Button not opening form to show linked data.

Did you create an append query to populate records in the junction table? If you have questions please reply with your table and field names.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Command Button not opening form to show linked data.

(OP)
My Tables:

FDApt
FieldNames:
FDAPointID (primary/autonumber)
Description: Text


FDAJunction
FieldNames:
InspectionID - (primary/number)
FDApointID - (primary/number)
COS - (y/n)
R - (y/n)
NonComp - (y/n)


Qry:

CODE -->

SELECT QryFDAjunction.FDApointID, FDApt.Description, QryFDAjunction.Cos, QryFDAjunction.R, QryFDAjunction.NonCompliance, QryFDAjunction.InspectionID
FROM QryFDAjunction RIGHT JOIN FDApt ON QryFDAjunction.FDApointID = FDApt.FDAPtID
WITH OWNERACCESS OPTION; 

This is what the form looks like when loads:


The only way the FDA form will populate is by entering the FDAptID # in the first column. Which I can live with, but if there is a way all these would populate so field person only needs to complete the last three columns would be fantastic!

Thanks again for your help! Hope you are not tiring with me. wink

RE: Command Button not opening form to show linked data.

You can create an append query with your FDApt table and the value from your Inspection ID on the form. Create a query with the following SQL that you can run when you click a

CODE --> SQL

INSERT INTO FDAJunction (InspectionID, FDAPointID)
SELECT Forms![Your Form Name Here]![InspectionID], FDAPointID 
FROM FDApt; 

Make sure you set a unique index on InspectionID, FDAPointID in the FDAJunction table since you don't want duplicates. I would actually add the junction table to the select part with a join and include all records from FDApt and where FDAJunction.InspectionID is null. You can also provide default values for other fields in the append query.

Once you run the append query you can requery the subform and all of your records will display.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Command Button not opening form to show linked data.

(OP)
Hi Dhookom!

Thank you for your help with this, I wouldn't have been able to accomplish this without your help! Never to old to learn! (I'm 70 yrs old and still fascinated with what Access can do and always something new to learn!
Have a great day!

RE: Command Button not opening form to show linked data.

Thanks monkeysee. I'm getting closer to 70 every day winky smile

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Command Button not opening form to show linked data.

(OP)
Hi Duane!

I finally have time to get back to this. Originally, I was not able to get your sql to work with the "forms!etc" clause. So I did it without that clause and appended to the FDAjunction table.
This worked for all existing inspections but not for any new inspection.

So going back to your original SQl:

CODE -->

INSERT INTO FDAJunction (InspectionID, FDAPointID)
SELECT Forms![FrmFDApt]![InspectionID], FDAPointID 
FROM FDApt; 
This is what I get in the design view:

And of course, if I try to run it, all kinds of errors pop up.
I have researched append queries and all I find is info on appending to another table, not to a form. What am I missing?

PS, FDApointID and InspectionID in the junction tables are set to unique index

Thanks again!

RE: Command Button not opening form to show linked data.

Do you have a form named FrmFDAPt with a control named InspectionID open with a value?
You need to use the field name from the table:

CODE --> SQL

INSERT INTO FDAJunction (InspectionID, FDAPointID)
SELECT Forms![FrmFDApt]![InspectionID], FDAPtID 
FROM FDApt; 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Command Button not opening form to show linked data.

(OP)
The FrmFDApt form is a sub form on FrmInsp
FrmInsp holds all basic info of insp and assigns an InspectionID # when the FrmInsp is opened.

The FrmFDApt (subform)has the InspectionID field (linked to FrmInsp)- the value of the field only populates once the FrmInsp automates with a new InspectionID.

RE: Command Button not opening form to show linked data.

You need to get your facts straight. Previously [26 Mar 19 20:02] you stated FDAPointID is the primary key in FDApt. Now it appears the field name is actually FDAPtID. This is troubling to say the least.

Do you understand what we are attempting to accomplish?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Command Button not opening form to show linked data.

(OP)
Duane,
Thank you for your patience.
I do understand what we are trying to accomplish, but am confused as to how to get there. I see I did make an error on the FDAptID in my table.
The hang up is still the form! expression.

Let's start from scratch: This is the sql for the record source of my form: frmFDApt

CODE -->

SELECT FDApt.Description, FDAJunction.InspectionID, FDAJunction.FDApointID, FDAJunction.Cos, FDAJunction.R, FDAJunction.NonCompliance
FROM FDApt INNER JOIN FDAJunction ON FDApt.FDAPtID = FDAJunction.FDApointID
WITH OWNERACCESS OPTION; 

Running the append code, the expression error comes up:

CODE -->

Expr1: Forms![FrmFDApt]![InspectionID] 

In the frmFDApt, InspectionID is from the FDAJunction Table (FDAJunction.InspectionID)
Should I be adding the Inspections Table to the record source which would be Inspections.InspectionID (Primary key in the Inspections Table)

Again thank you for your help and patience!

RE: Command Button not opening form to show linked data.

I assume you want to add records to FDAJunction for the current InspectionID and every unique Point. InspectionID should be on the main form (FrmInsp). Try this SQL:

CODE --> SQL

INSERT INTO FDAJunction (InspectionID, FDAPointID)
SELECT Forms![FrmInsp]![InspectionID], FDAPtID 
FROM FDApt; 


Again, the two fields in FDApt should be a unique index to remove any possibility of duplicates. You can also use a left join to FDApt and filter out where FDApt contains the records.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Command Button not opening form to show linked data.

(OP)
Hi Duane!
Low and behold I got it to work!
BUT I did still get the error that the 'InspectionID' in the form expression could not be found. So I did some more research on web and found that this is a common error with this type of expression. And had to build a "Public Function" to force Access to recognize the InspectionID. This is what I did:

CODE -->

Public Function FormFieldValue(FormName as String, FieldName As String)
FromFieldValue=Forms(FrmInsp).Controls(InspectionID)
EndFunction 

Thank you so much for your help and time, I wouldn't have been able to do it without your help!!!!

Have a great day!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close