Problems with Opening form and passing link (1 Viewer)

JudyHNM

Registered User.
Local time
Today, 12:10
Joined
Oct 5, 2006
Messages
37
I have created a main form and a related form that I call with the click of a command button. I am trying to pass the value in the linking field to the called form, and it just isn't working. The called form "knows" it's being filtered but doesn't have the value I passed it in the correct field. I have no idea what I am doing wrong and am hoping someone can help me.

tblCD_SupplyID - this is the field I am trying to populate on the called form
tblS_ID - is the field on the calling form that contains the value

I have used a msgbox and tracing to make sure the correct value is in tblS-ID, and it is.

Code:
Private Sub cmdCraftDetail_Click()
'---------------------------------------------------------------------
'Purpose:   Open the subformfrmSub_CraftDetail when button is clicked
'           and sync the supply id on the two forms.  Issue error if
'           there's no information on the main form
'Date:      05/18/17
'
'Note:      This is NOT working! It opens the form but doesn't populate
'           the linked field with the supply ID
'---------------------------------------------------------------------

On Error GoTo err_cmdCraftDetail_Click

    Dim strDocName      As String   'name of form to be ordered
    Dim strLinkCriteria As String
    Dim strTitle        As String
    Dim strMessage      As String
    Dim intButton       As Integer
    Dim intChoice       As Integer
    
    strDocName = "frmSub_CraftDetail"
    
    strLinkCriteria = "[tblCD_SupplyID]=" & Me![tblS_ID]
    
   ' Example: DoCmd.OpenForm "frmMainEmployees", , , "DepartmentID=" & cboDept.Value
   'MsgBox strLinkCriteria
   
   'DoCmd.OpenForm strDocName, , , "tblCD_SupplyID=" & tblS_ID.Value
    
    DoCmd.OpenForm strDocName, , , strLinkCriteria
    
Exit_cmdOrderType_Click:
    Exit Sub
    
err_cmdCraftDetail_Click:
    strTitle = "Supply Error"
    If Err.Number = 3075 Then
        strMessage = "you must enter the Supply Name before " & _
            vbCrLf & "opening the Craft Detail form"
    Else
        strMessage = Err.Number & ": " & Error.Description
    End If
    
    intButton = vbOKOnly + vbExclamation + vbDefaultButton1
    intChoice = MsgBox(strMessage, intButton, strTitle)
    
    Resume Exit_cmdOrderType_Click


End Sub

Please help. Thanks -- Judy
 

JudyHNM

Registered User.
Local time
Today, 12:10
Joined
Oct 5, 2006
Messages
37
Thanks for the information -- that's a great reference to have, but this hasn't fixed my problem.

tblS_ID is an autonumber field (long integer) in the Main form
tblCD_SupplyID is a long integer field in the subform.

When I trace the execution of the module, I find the following
Code:
?strLinkCriteria
[tblCD_SupplyID]=1
in the link criteria for DoCmd. This is correct.

However, it never gets to the subform. When I trace the loading & opening of the subform, tblCD_SupplyId has a value of 0 (zero); so, it is never receiving the data I pass.

I am stuck.
Judy
 

JHB

Have been here a while
Local time
Today, 19:10
Joined
Jun 17, 2012
Messages
7,732
...
tblCD_SupplyID - this is the field I am trying to populate on the called form
..
How do you populate the field, (then you've not shown any code for it), or asked in another way, how do you think tblCD_SupplyID should get populate?
 

JudyHNM

Registered User.
Local time
Today, 12:10
Joined
Oct 5, 2006
Messages
37
I posted the code in the first message in this thread.
 

JHB

Have been here a while
Local time
Today, 19:10
Joined
Jun 17, 2012
Messages
7,732
I posted the code in the first message in this thread.
Yes you posted some code, but nothing in that code populate tblCD_SupplyID, therefore I asked, how do you think tblCD_SupplyID should get populated?
 

JudyHNM

Registered User.
Local time
Today, 12:10
Joined
Oct 5, 2006
Messages
37
Yes you posted some code, but nothing in that code populate tblCD_SupplyID, therefore I asked, how do you think tblCD_SupplyID should get populated?

This is how I populated
Code:
strLinkCriteria = "[tblCD_SupplyID]=" & Me![tblS_ID]
then I used this string in
Code:
DoCmd.OpenForm strDocName, , , strLinkCriteria
I also tried it using this alternative:
Code:
DoCmd.OpenForm strDocName, , , "tblCD_SupplyID=" & tblS_ID.Value

When I have done this before in another database I created, it automatically populated tblCD_SupplyID in the subform when it opened.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:10
Joined
Aug 30, 2003
Messages
36,118
That code only fliters the main form. The only way it could affect a subform would be with master/child links on that field.
 

JudyHNM

Registered User.
Local time
Today, 12:10
Joined
Oct 5, 2006
Messages
37
I have set up the master/child links as shown in the attached the file. As you will see the Master is tblS_CraftSupplies and there are two child forms. tblChild_SuppliesPurchase is a subform on the main form page and is working perfectly. tblChild_CraftDetail is the one that is called by the
Code:
DoCmd.OpenForm strDocName, , , strLinkCriteria
code.

I just don't see where I am making my mistake.

Thanks, Judy
 

Attachments

  • Craft Supplies Links.png
    Craft Supplies Links.png
    17.3 KB · Views: 59

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:10
Joined
Aug 30, 2003
Messages
36,118
What are the master/child link properties of the subform controls?
 

JudyHNM

Registered User.
Local time
Today, 12:10
Joined
Oct 5, 2006
Messages
37
What are the master/child link properties of the subform controls?

I am not sure what you are asking. I opened the subform in design view and selected the "Data" tab. It doesn't have anything about link properties -- it just shows a filter.

I opened the subform that is actually embedded in the main form (rather than accessed via a command box), and it does have link properties.

Is this the type of information you are asking about? If so, did I create the subform that is accessed via a command box incorrectly? I looked at the other database I created using this type of setup, and it works perfectly.

Obviously, I am a bit lost at this point.

Judy
 

JHB

Have been here a while
Local time
Today, 19:10
Joined
Jun 17, 2012
Messages
7,732
Could you post your database with some sample data, (zip it) + a description how to see your problem.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:10
Joined
Aug 30, 2003
Messages
36,118
Is this the type of information you are asking about?

No, what I'm looking for are the master/child link properties of the subform control on the main form. If the main form is in design view, clicking once on the subform should get you the properties of the subform control. Clicking a second time gives you the properties of the subform itself.
 

frustrating

Registered User.
Local time
Today, 11:10
Joined
Oct 18, 2012
Messages
68
The only thing I can think of without seeing the database is the syntax for your where clause.
Would adding this help?
strLinkCriteria = "[tblCD_SupplyID]=" & chr(32) & Me![tblS_ID] & chr(32)
 

Users who are viewing this thread

Top Bottom