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]

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

 

[monkeysee]

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!

 

[dhookom]

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

 

[monkeysee]

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

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!

 

[dhookom]

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

 

[monkeysee]

My Tables:

[highlight #FCE94F]FDApt
FieldNames:
FDAPointID (primary/autonumber)
Description: Text[/highlight]

[highlight #FCAF3E]FDAJunction
FieldNames:
InspectionID - (primary/number)
FDApointID - (primary/number)
COS - (y/n)
R - (y/n)
NonComp - (y/n)[/highlight]

Qry:

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.

 

[dhookom]

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

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

 

[monkeysee]

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!

 

[dhookom]

Thanks monkeysee. I'm getting closer to 70 every day ;-)

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

 

[monkeysee]

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:

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!

 

[dhookom]

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:

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

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

 

[monkeysee]

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.

 

[dhookom]

You need to get your facts straight. Previously [26 Mar 19 20:02] you stated [highlight #FCE94F]FDAPointID[/highlight] is the primary key in FDApt. Now it appears the field name is actually [highlight #FCE94F]FDAPtID[/highlight]. 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

 

[monkeysee]

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

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:

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!

 

[dhookom]

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:

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

 

[monkeysee]

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:

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!