Insert record into different table after update

[dkmoreland]

I am trying to update one table depending on the value entered on a form and could use some insight from those that know more than I do.

Here's the scenario:

Input table:

ID - auto number
Accept/Recheck field - Quality control evaluation status
There are other fields in this table but I just need to concentrate on those

Non Conformance Table:
ID - number field - not auto number
NCR # - number field


When the user enters a status of "recheck" on the form for the Input table, I am querying the Non Conformance table to find out the last NCR # used. Once I know that, I need to increment that NCR # by 1, then add a new record to the Non Conformance table. The record should contain the new NCR # and the ID number from the Input table.


The remaining fields in the Non Conformance table will be populated when the user fills out a different form.

I am assuming the code for this would go in the After Update property for the Accept/Recheck text box. But I am unsure of the code to increment the NCR # and the insert statement.

Any help would be greatly appreciated.

Thanks,
Diana

 

[isladogs]

First of all you should not use 'special' characters like # or / in table or field names.
Doing so means you have to enclose the name in [] brackets and can cause unexpected issues.
I would also recommend no spaces in field names.

Next is there a good reason for not using an autonumber field?
It's not essential to do this but it makes life much easier.

Anyway you need

DMax("[NCR #]",["[Non Performance]")+1

As for the append query, are the ID values the same in the 2 tables for the same record?

 

[dkmoreland]

Yes, I am aware that special characters or spaces should not be used in table or field names. This system was created years ago by someone else and I have been tasked with updating and fixing some problems.

I can't change the data type to autonumber since this is a production database and there is historical data that can't be lost.

Yes, in the append query, the ID values would be the same for the same record. For example, in the Input table, the ID is 12345. In the Non Conformance table, the last NCR # used is 788. I need a new record in the Non Conformance table with an ID of 12345 and a NCR # of 789. But this should only happen if Accept/Recheck = "Recheck".

Thanks

 

[isladogs]

Doing the append query proved a bit more complex than expected, mainly due to the 'naming convention' used.
Hopefully no typos / syntax errors!

This assumes you have a checkbox on the form called Accept_Recheck.
Use the On Click event

Private Sub Accept_Recheck_Click()

    Dim lngNCR As Long
    lngNCR = DMax("[NCR #]", "[Non Conformance]")
   ' Debug.Print lngNCR
    
    If Me.Accept_Recheck = True Then
        CurrentDb.Execute "INSERT INTO [Non Conformance] ( ID, [NCR #] )" & _
            " SELECT " & Me.ID & " AS NewID, " & (lngNCR + 1) & " AS NewNCR" & _
            " FROM [Input] LEFT JOIN [Non Conformance] ON Input.ID = [Non Conformance].ID" & _
            " WHERE ((([Non Conformance].ID) Is Null));"
    End If
End Sub

If it's a textbox, use the same code but in the After Update event

 

[arnelgp]

you should do the Inserting of Record to [Non Conformance]
table to the Form's Before Update event and not on
control Accept/Recheck's Before or After Update event.
the reason is the user might just click and click
on the checkbox, therefore new records will be
created on [Non Conformance] table everytime he does that.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim lngLastNCR As Long
    If ((Me![Accept/Recheck]) = True) Then
        lngLastNCR = Nz(DMax("[NCR #]", "[Non Conformance]"), 0) + 1
        CurrentDb.Execute "Insert Into [Non Conformance] (ID, [NCR #]) " & _
            "SELECT " & Me.ID & ", " & lngLastNCR
	Msgbox "A new Non Conformance record has been created."
    End If
End Sub

 

[isladogs]

Arnel made a good point.
However, I specifically checked that point and ensured it didn't happen with the code I posted.
Same ID value as PK told in both tables so no duplicates

 

[dkmoreland]

Thanks for the help but I ran into a problem. To verify that the user does indeed intend to create the new NCR, I put a verification dialog in the After Update property of the Accept/Recheck control (which is a combobox).

Then, I put this code in the On Click property of the Continue button on the continuation dialog:

 Private Sub CmdNewNCR_Click()
  Dim lngLastNCR As Long
    If ((Me![Accept/Recheck]) = "Recheck") Then
        lngLastNCR = Nz(DMax("[NCR #]", "[Non Conformance]"), 0) + 1
        CurrentDb.Execute "Insert Into [Non Conformance] (ID, [NCR #]) " & _
            "SELECT " & Me.ID & ", " & lngLastNCR
    MsgBox "A new Non Conformance record has been created."
    End If
End Sub

But, I'm getting a compile error - "method or data member not found"

What am I missing?

Thanks

 

[isladogs]

You could try removing all four ( and ) brackets in the If line but I doubt that's the issue

Or try my version of the code which I tested and know that it works

 

[dkmoreland]

I removed the verification step just to simplify things until I get this working and used your exact code in the After Update property.

Dim lngNCR As Long
    lngNCR = DMax("[NCR #]", "[Non Conformance]")
   ' Debug.Print lngNCR
    
    If Me.Accept_Recheck = True Then
        CurrentDb.Execute "INSERT INTO [Non Conformance] ( ID, [NCR #] )" & _
            " SELECT " & Me.ID & " AS NewID, " & (lngNCR + 1) & " AS NewNCR" & _
            " FROM [Input] LEFT JOIN [Non Conformance] ON Input.ID = [Non Conformance].ID" & _
            " WHERE ((([Non Conformance].ID) Is Null));"
    End If

However, I am getting this:
Run time error 2471
The expression you entered as a query parameter produced this error '[NCR]'.

I'm wondering if this is caused by the fact that the NCR # that I am trying to increment only exists in the Non Conformance table.

Suggestions?

 

[isladogs]

I'm wondering if this is caused by the fact that the NCR # that I am trying to increment only exists in the Non Conformance table.

No - that was clear from post 1

Attached is the test database I used before posting my solution
Form1 uses a checkbox. Form2 uses a textbox
Suggest you look at that to confirm it does what you want

Comparing your code in post 7 with mine in post 4 there are a few differences:
1. My control is called Accept_Recheck. Yours is Accept/Recheck
2. I understood the field was a Yes/No (boolean) field - hence the line:

If Me.Accept_Recheck = True Then

You appear to have a text field:

If ((Me![Accept/Recheck]) = "Recheck") Then

If you sort out the correct terminology for your own database it should work

 

[dkmoreland]

yes, Accept/Recheck is a text field - the control is a combo box. Like I said, I inherited this system.

Anyway, I did make the terminology changes but I'm still getting this runtime error.

Run time error 2471
The expression you entered as a query parameter produced this error '[NCR]'.

The code the debugger is landing on is this:

    lngNCR = DMax("[NCR #]", "[Non Conformance]")

Thanks

 

[isladogs]

The fact that the error shows [NCR] rather than [NCR #] suggests that somewhere you have omitted the square brackets around this field
If so, Access will then start looking for a date based on the #

Did you check that my sample db was working in the way you wanted?

 

[dkmoreland]

Yes - I did check the sample db. It was indeed working the way I wanted.

I found the problem causing the run time error - the field name in the table is NCR# without a space. Once I removed the space, it worked flawlessly.

I am grateful for the help - I learn so much from the people on this forum who are willing to take their time to assist others.

Thanks so much.