Finishing off a Form - sub form link. Help please

[maxxev]

Hi, regarding the below code:

Private Sub Command15_Click()
On Error GoTo Err_Command15_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "S_frmIngDecSubComp_COO"
    
If IsNull(DLookup("SubComp_in_ingID", "tblSubComp_COO", "SubComp_in_ingID=" & Me![SubComp_in_ingID])) Then
    DoCmd.OpenForm "S_frmIngDecSubComp_COO", , , , acFormAdd
    Forms!S_frmIngDecSubComp_COO.SubComp_in_ingID = Me![SubComp_in_ingID]
Else
   DoCmd.OpenForm "S_frmIngDecSubComp_COO", , , "[SubComp_in_ingID]=" & Me![SubComp_in_ingID]
End If

Exit_Command15_Click:
    Exit Sub

Err_Command15_Click:
    If StandardErrors(Err) = False Then
    MsgBox Err & ": " & Err.Description
    End If
    'MsgBox Err.Description
    Resume Exit_Command15_Click
    
End Sub

The above code seems to work for the first new entry but any new entires come up with a "SubComp_in_ingID" value of "0", how do I get the new cell values for "SubComp_in_ingID" in my called form to also have the "SubComp_in_ingID" value pulled from the above code?

Thank you.

 

[kjv1611]

It sounds like SubComp_in_ingID is being set to 0 at each attempt, and that Access is telling you that you can't open the subform for that value, b/c it either doesn't exist in the subform's record source, or either is just not logical.

How is SubComp_in_ingID being populated before the command button is clicked?

--

"If to err is human, then I must be some kind of human!" -Me

 

[GKChesterton]

    If IsNull(DLookup("SubComp_in_ingID", _
        "tblSubComp_COO", "SubComp_in_ingID=" & yourIndex)) Then

      '' Use an action query or recordset and add new yourIndex record
      
    End If

    DoCmd.OpenForm "S_frmIngDecSubComp_COO", , , "[SubComp_in_ingID]=" & yourIndex

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[maxxev]

Hi, thank you for your response.

The button is on a line on a continous form, the line contains the "SubComp_in_ingID".

I believe that it is working correctly as when checking the called "S_frmIngDecSubComp_COO" form is does state "[SubComp_in_ingID]=6" in the "filter" section of the form properties.

It has the correct reference when opening the called "S_frmIngDecSubComp_COO" form where there are no existing entires, it's only additional entries where it is not pulling through the reference.

Cheers

 

[maxxev]

Thank you GKChesterton for your response, however i'm afraid i'm no good at programming, everything I have done is copied from else where and very basically modified, I don't understand what you are explaining. Sorry.

Cheers

 

[GKChesterton]

DoCmd.OpenForm "S_frmIngDecSubComp_COO", , , , acFormAdd

The line above won't start you off with the index in the needed field. It's a useful line but not at the level of form automation you desire.

yourIndex is a variable for Me![SubComp_in_ingID]. Again for this level of automation, you must start learning first things such as using a variable. (For me, learning means getting a book.)

(The code you posted has examples of variables. Your work doesn't use them; but their inclusion doesn't keep the code from functioning. I just mention it as general information.)

As for the exciting part:

Use an action query or recordset

...the former should be easier for you as you begin.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[maxxev]

Hi, thank you for the pointers.

Can I revise the question, in order to have a quick fix in order to try and show people what we can do with access, I just need this last form to work.

How can I make a continous sub form, use a txt box value it's master form the same sheet in order to fill in one of it's cells:

i.e. trying to bodge the above issue.
If call a form which saves into a txt box the "SubComp_in_ingID" value, can I get the subform to use this value for all of its "SubComp_in_ingID" cells?

Cheers

 

[maxxev]

Hi, sorry but nevermind, I seem to have cracked this last request on my own!

Cheers

 

[GKChesterton]

Hey, if it "show people what we can do with access," it works for me!