linked subform

[sandy6078]

I am designing a database for our engineering group. In the database I have the following tables; tProgram, tPartFamily, tAssembly, tSubAssembly, tSub2Assembly, tComponent, tMaterial and tProcess. I have attached a relationship screenshot.

Using search I have found a partial answer to my problem. Like jeremycod I would like a pop up form that is populated with the Parent id number. I found the answer in thread 134769. I have fPartFamily nested in the fProgram form. I want to be able to point to a record in PartFamily and click a command button to open the fAssembly form and edit and add records related to the active record.

Now for the problem. The code works great when tested prior to nesting fPartFamily in fProgram. However, when fPartFamily is nested and I click the command button I get the error message that is written into the code and the message that the open form action was canceled. Can you help me with the code?

sandy

 

[Access_guy49]

My first question for you is, how are you passing the PFID number to your assembly form when you click the command button? is it via a default value or through your code? also could you post the code of the command that gives you the error?

 

[sandy6078]

I am using code to pass the PFID umber to the assembly form, here is the code for the command button.

Private Sub cmdOpenAsmForm_Click()
On Error GoTo Err_cmdOpenAsmForm_Click

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "fAssembly"

strLinkCriteria = "[PFID]=" & Me![PFID]
DoCmd.OpenForm strDocName, , , strLinkCriteria
Me.Refresh

Exit_cmdOpenAsmForm_Click:
Exit Sub

Err_cmdOpenAsmForm_Click:
MsgBox Err.Description
Resume Exit_cmdOpenAsmForm_Click

End Sub

 

[Access_guy49]

it seems to me that your code would likely bug out on the lines:

strLinkCriteria = "[PFID]=" & Me![PFID]
DoCmd.OpenForm strDocName, , , strLinkCriteria

(personally i have never used Me![PFID] I always use PFID.text (or .value) but i don't really know that there is a difference there)
my guess would be that with this criteria the open form command is looking for your assembly form to have the PFID number in the assembly PFID field. which, if your opening on a record that does not have an existing Assembly record, it would not be able to link. (i hope that makes sense) in your design view of your assembly form try making the default value of your PFID = your PFID from the partfamily form. that way if the assembly PFID is blank it will be given the value needed to be accepted by your linkcriteria.

 

[sandy6078]

I apologize, I did not inclue the on current code for fPartFamily or the code for fAssembly or the module. Perhaps this will explain further.

As I stated in the original post, the code works when fPartFamily is not nested in the fProgram form.

code for fProgram

Private Sub cmdOpenAsmForm_Click()
On Error GoTo Err_cmdOpenAsmForm_Click

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "fAssembly"

strLinkCriteria = "[PFID]=" & Me![PFID]
DoCmd.OpenForm strDocName, , , strLinkCriteria
Me.Refresh

Exit_cmdOpenAsmForm_Click:
Exit Sub

Err_cmdOpenAsmForm_Click:
MsgBox Err.Description
Resume Exit_cmdOpenAsmForm_Click

End Sub


Private Sub Form_Current()
On Error GoTo Form_Current_Err

If (IsLoaded("fAssembly")) Then
DoCmd.OpenForm "fAssembly", acNormal, "", "PFID=Forms!fPartFamily.form.PFID", acFormEdit, acWindowNormal
End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit
End Sub

Private Sub Form_Current()
Dim iMain As Integer

iMain = Forms!fPartFamily.Form.PFID

If Me.NewRecord = True Then
Me.PFID = iMain
Me.Caption = "New Record"
Else
Me.Caption = Forms!fPartFamily.Form.PartFamily
End If

End Sub

code for fAssembly

Private Sub Form_Open(Cancel As Integer)

Dim strMsg As String
Dim strTitle As String
Dim intStyle As Integer
Dim strFName As String
Dim strCForm As String

strFName = "fPartFamily"
strCForm = Me.Form.Name

If IsLoaded(strFName) Then
Exit Sub

Else
strMsg = "You cannot open" & " " & strCForm & " " & "as a standalone form." & vbCrLf & _
"The Assembly Form must be opened from" & " " & strFName

intStyle = vbOKOnly
strTitle = "Can't Open Form"
MsgBox strMsg, intStyle, strTitle
Cancel = -1
DoCmd.Close
End If

End Sub

Module

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Dim oAccessObject As AccessObject

Set oAccessObject = CurrentProject.AllForms(strFormName)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If

End Function

 

[sandy6078]

the 1st code is for fPartFamily not fProgram.

 

[Access_guy49]

Do you know where the code bugs out? have you put a stop at the start of your code and walked through it to make sure your Module and all if statements are working properly? (sorry for the questions, it's just that the code looks pretty solid to me)

 

[sandy6078]

The code bugs out here -

strLinkCriteria = "[PFID]=" & Me![PFID]
DoCmd.OpenForm strDocName, , , strLinkCriteria

What I don't understand is why the code will work when fPartFamily is not nested. When I put a stop in the code, this error message popped up ** Syntax error (missing operator) in query expression '[PFID='.

 

[Access_guy49]

i have a potential idea. and i think it has to do with the me!
if you open up the partfamily by itself (so that it's not nested) the code works perfectly.
however when you open it nested, you are not actually opening partfamily. therefor Me! is not refering to the nested form it is refering to the parent form, which does not have a PFID. so my guess is that you can't use PFID = ME![PFID] but rather PFID = SUBFORMNAME![PFID] (but this is just a guess)

 

[sandy6078]

I tried that and get a dialog box requesting a parameter value for fPartFamily.PFID

any other ideas? I keep trying different things but I am very limited in vba.

Sandy

 

[Access_guy49]

do you have it as Form!fpartfamily.PFID just out of curiosity?

 

[sandy6078]

Yes and many other variations I have also tried this using my forms and controls that I found in the help section.
********************
The next two examples show how you might refer to a control named NewData on a subform ctlSubForm contained in the form called OrderForm:

Forms!OrderForm.ctlSubForm.Form!Controls.NewData

Forms!OrderForm.ctlSubForm!NewData
****************************
I have abused the limit of ibuprofin one should take in a day because of this problem and I am ready to start on liquid sedatives (the kind with the foam at the top). Unfortuneately I have 2 hours before I can do that without fear of being walked off the job.

 

[sandy6078]

I was wondering if anyone else might hava a solution to my problem? Any help would be most welcome.

Sandy

 

[sandy6078]

Hi,

I was wondering if anyone might have a solution to my problem?

Thanks for your help.

Sandy

 

[boblarson]

Can you post your database, or email it if compacting (Tools > Database Utilities > Compact and Repair) and then zipping (with WinZip or some other zip utility) has it still too large (393KB or less)?

 

[sandy6078]

Thank you so much for your help. I noodled around and changed the forms so that I could open the form with the attached code that I orginally had the question on. Double click on the part family field in fMainProgram and fMainPartFamily opens. However, I'm not sure if I am approaching this database in the most efficient way.

The database will track parts in an automotive supplier program. The Program is broken down into Part Families and from there Assemblies. An assembly could possibly comprise of individual componets, sub-assemblies (which would be a sub assembly number with its own components) and possibly sub-subassembly again with its own number and its own components. For example you could have a screw which is used in a subassembly but is also used in the assembly. I hope I have not confused you. I have also revised the relationships by adding a junction table.

From every angle I have approached the design of this database it seems like it will be a data input nightmare. Ultimately the engineers will also want to sort by number, whether it is an assembly, sub, subsub or component and get all related information and history.

Once again, thank you for any help that you can give me it is very very much appreciated.

Sandy

 

[sandy6078]

I forgot to mention I had just started breaking down the various part numbers into three different fields when I attached the database.

Sandy

 

[sandy6078]

I found a solution to my original problem of linking criteria for a subform. Please see attached link.

http://support.microsoft.com/kb/113352/en-us

I am still working on coming up with an easy design for the person(s) who will have to input the engineering information. Some programs will have thousands of parts. Then there are the engineering changes that will take place. This database is an important performance indicatator for me and I am terribly afraid of mucking it up.

Any suggestions and/or help would be greatly appreciated. Thank you Access Guy for your input and BobLarson if you have any suggestions please bring them on.

Sandy