Syntax

[mike60smart]

Hi everyone
I have posted the same question on Utter Access

The code I am using as as follows and I am getting the following error.

Private Sub cmdAddLines_Click()
10    On Error GoTo cmdAddLines_Click_Err

20          If Me.Dirty Then Me.Dirty = False

30          If DCount("[MainId]", "tblLineUnitEntries", "[MainId] = " & Me.txtMainID) <> 0 Then
40            Me.Undo

50          If MsgBox("These Line Items already exist", vbOKCancel) = vbOK Then
60              Exit Sub
70          Else
80                  DoCmd.RunMacro "mcrAddLines" 'Runs an append query tp add a specific number of records
      
                Dim strSQL As String

90          strSQL = "UPDATE tblLineUnitEntries SET MainID = " & Me.txtMainID _
                   & " WHERE MainID Is Null;"
100         Debug.Print strSQL
110         CurrentDb.Execute strSQL, dbFailOnError

120         [Forms]![frmMachines]![frmMainData]![frmLineUnitEntriesSubform].Requery

130         End If
140         Exit Sub

cmdAddLines_Click_Exit:
150       Exit Sub

cmdAddLines_Click_Err:
160       MsgBox Error$
170       Resume cmdAddLines_Click_Exit

End Sub

Any help appreciated

 

[theDBguy]

Try moving line 40 into the end of line 30 and eliminate line 40.

 

[mike60smart]

Try moving line 40 into the end of line 30 and eliminate line 40.

Hi
I changed the code as suggested

Private Sub cmdAddLines_Click()
10    On Error GoTo cmdAddLines_Click_Err

20          If Me.Dirty Then Me.Dirty = False

30          If DCount("[MainId]", "tblLineUnitEntries", "[MainId] = " & Me.txtMainID) <> 0 Then Me.Undo


40          If MsgBox("If These Line Items already exist then click Cancel - it they do not exist click OK", vbOKCancel) = vbCancel Then
50              Exit Sub
60          Else
70                  DoCmd.RunMacro "mcrAddLines" 'Runs an append query tp add a specific number of records

                Dim strSQL As String

80          strSQL = "UPDATE tblLineUnitEntries SET MainID = " & Me.txtMainID _
                   & " WHERE MainID Is Null;"
90          Debug.Print strSQL
100         CurrentDb.Execute strSQL, dbFailOnError

110         [Forms]![frmMachines]![frmMainData]![frmLineUnitEntriesSubform].Requery

120         End If
130         Exit Sub
cmdAddLines_Click_Exit:
140       Exit Sub

cmdAddLines_Click_Err:
150       MsgBox Error$
160       Resume cmdAddLines_Click_Exit

End Sub

It now does what I want.
Thanks for the guidance.

 

[theDBguy]

Hi
I changed the code as suggested

Private Sub cmdAddLines_Click()
10    On Error GoTo cmdAddLines_Click_Err

20          If Me.Dirty Then Me.Dirty = False

30          If DCount("[MainId]", "tblLineUnitEntries", "[MainId] = " & Me.txtMainID) <> 0 Then Me.Undo


40          If MsgBox("If These Line Items already exist then click Cancel - it they do not exist click OK", vbOKCancel) = vbCancel Then
50              Exit Sub
60          Else
70                  DoCmd.RunMacro "mcrAddLines" 'Runs an append query tp add a specific number of records

                Dim strSQL As String

80          strSQL = "UPDATE tblLineUnitEntries SET MainID = " & Me.txtMainID _
                   & " WHERE MainID Is Null;"
90          Debug.Print strSQL
100         CurrentDb.Execute strSQL, dbFailOnError

110         [Forms]![frmMachines]![frmMainData]![frmLineUnitEntriesSubform].Requery

120         End If
130         Exit Sub
cmdAddLines_Click_Exit:
140       Exit Sub

cmdAddLines_Click_Err:
150       MsgBox Error$
160       Resume cmdAddLines_Click_Exit

End Sub

It now does what I want.
Thanks for the guidance.

Glad to hear you got it sorted out. It pays to be consistent with your code structure. Cheers!

 

[The_Doc_Man]

I see that theDBguy fixed you up.

Just by way of explanation, there are TWO kinds of IF statements. The in-line IF and the block IF.

The trivial way to distinguish them is that a block IF statement starts with the word IF, provides condition tests, and ends on the same line as the IF but with the word THEN and nothing else (except of course, possibly a comment.) The in-line IF just contains a valid statement fragment after the word THEN on the same line as the initial IF. The block-beginning IF statement requires an END IF - sort of like balanced parentheses but vertically. The in-line IF does not need - or want - an END IF statement.

The thing that theDBguy told you to do certainly worked. You just made the block IF into an in-line IF. The OTHER way to fix that would have been to put a space and an underscore ( _) behind the THEN statement on your original line 30 and remove the numbering from line 40 - but not the code. The underscore is used to signify a continuation line, which would have meant that the Me.Undo was considered a continued part of line 30, which would have made line 30 into an in-line IF that just happened to span two printed lines.

 

[arnelgp]

one comment on your code on post #3.
You already Saved the form (Me.Dirty = False), therefore there is no way you can Undo (Me.Undo) the changes you made.

 

[Pat Hartman]

I don't use the in line IF because when scanning the code, the action seems to disappear and I also keep looking for the End If. Just an opinion from someone with old eyes. Using in line IF's is not technically wrong. They are a part of the language after all.

In addition to arnelgp's comment on the save being in the wrong place. There are lots of ways to trigger saving a record. THEREFORE,
1. most validation should occur in the Form's BeforeUpdate event, not in an event you code that tells Access to save because they the validation only occurs when YOU specifically tell Access to save and never when the user takes an action that Microsoft think requires a save.
2. if you want something to always happen AFTER the record gets saved, the best event to use is the Form's AfterUpdate event. In the case of the form above, the update won't run if Access takes it upon itself to save the record.
3. you are running an update query because you seem to be saving a record at the wrong time with incomplete data so it wouldn't pass your validation logic if you had any and also probably don't have the master/child links set correctly to force Access to populate the FK.

Hate to be harsh but my grade is a C-. The code will work once you make the recommended fixes, usually, but not always.

 

[The_Doc_Man]

I don't use the in line IF because when scanning the code, the action seems to disappear and I also keep looking for the End If. Just an opinion from someone with old eyes. Using in line IF's is not technically wrong. They are a part of the language after all.

I concur with your reasoning, Pat. That is the same reason for me to avoid in-line IF statements except very sparingly, like sticking in a conditional EXIT SUB when the logic has gotten too convoluted to read easily. My eyes aren't young either.

 

[Mark_]

@OP,
Just to make sure, this is intended to be a single user system that cannot allow two users to work on Parent records at the same time?
The " Where MainID is Null;" means if you have more than one person entering entries in tblLineUnitEntries at the same time, who ever trips this code FIRST gets all of the line entries made.

Were this me, if MainID needs to be filled in, I'd never allow tblLineUnitEntries records to be entered prior to this value being established. This may work most of the time, but when it fails you'll have some very confused people and a really hard time trying to track down what went wrong. Future you will be very cross with past you when you have to go back in and change this.