Command Button not opening form to show linked data. 1

[monkeysee]

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

 

[dhookom]

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?

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

 

[monkeysee]

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

 

[dhookom]

What is the forms record source?

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

 

[Andrzejek]

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


---- Andy

There is a great need for a sarcasm font.

 

[monkeysee]

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:

 

[Andrzejek]

Your criteria sets [tt]Insp[highlight #FCE94F]I[/highlight]D[/tt] field, but your FieldName is [tt]InspD[/tt] (with missing [tt]I[/tt])


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Good catch Andy!

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

 

[Andrzejek]

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


---- Andy

There is a great need for a sarcasm font.

 

[monkeysee]

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.

 

[dhookom]

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

 

[monkeysee]

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

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:

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)

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!

 

[dhookom]

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

 

[monkeysee]

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!

 

[dhookom]

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

 

[monkeysee]

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?

 

[dhookom]

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

 

[monkeysee]

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.

 

[dhookom]

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

 

[monkeysee]

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?