Insert statement help

[midiman69]

I have a subform bound to the main form by two primary keys - Xfile and issueno.

I have a routine on a subform field that inserts a record in a table if no matching records found using

If DCount("*", "qrybom", "partno = '" & partno & "'") = 0 Then
If MsgBox("This is a New Part - Do You Wish To Add?", _
vbYesNo, "Project Costing Database") = vbNo Then
Exit Sub

Else: DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " _
& "Values ('" & partno & "')"


End If
End If


End Sub

This works ok but I really need to include the Xfile and issueno fields on the main form so that the "new" record is bound to that record. Can any one suggest a way I can achieve this??

Cheers

Dave

 

[JerryKlmns]

Guessing that Xfile & issueno live in a textbox, you could get those values from ...

Forms!(MainFormName)!Controls.Item("txtXfile").Value
Forms!(MainFormName)!Controls.Item("txtIssueNo").Value

 

[Remou]

How about:
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno, xfile, issueno ) " _
& "Values ('" & partno & "', '" & me.xfile & "', '" & me.issueno & "')"
The above assumes that all fields are text. Leave out the single quotes (') for numeric fields.

 

[midiman69]

Thanks Guys this Works fine - Cheers

DoCmd.RunSQL "INSERT INTO tblnewparts ( partno, xfile, issueno ) " _
& "Values ('" & partno & "', '" & Forms!frmprojectstabbed.Form!xfile & "', '" & Forms!frmprojectstabbed.Form!issueno & "')"

How do I remove the entry that has been added to tblnewparts from the subform? I have tried using [partno]="" but this cancels the insert statement.

Thanks for your help

Dave

 

[Remou]

Are you talking about a delete query?

 

[midiman69]

Hi Remou,

Yes I suppose so - if a matching record for a part number is not found on the subform then the "new" part no is added to tblnewparts - I need to be able to remove the entry on the subform so that I don't get the "can't find matching record" error

 

[Remou]

As in someone typed in 'ABC123' and it did not exist, so you want to clear the typing? Have you looked at Undo (Me.Whatever.Undo)? You may need acDataErrContinue, I am not sure I get what is happening.

 

[midiman69]

Yes exactly, remove the typing once it has been added to the new parts table or if the new part is rejected.
I have tried If MsgBox("This is a New Part - Do You Wish To Add?", _
vbYesNo, "Project Costing Database") = vbNo Then Me.partno.Undo
Exit Sub

But this doesn't work.

Thanks fro your help Remou

 

[Remou]

Which event are you using? Have you considered Before Update and Cancel?

 

[midiman69]

Hi Remou,

I am using the before update event. I have tried If MsgBox("This is a New Part - Do You Wish To Add?", _
vbYesNo, "Project Costing Database") = vbNo Then cancel=True
Exit Sub
With no luck

 

[Remou]

Maybe:

Private Sub Field1_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblnewparts", "partno = '" & partno & "'") = 0 Then
    If MsgBox("This is a New Part - Do You Wish To Add?", _
        vbYesNo, "Project Costing Database") = vbNo Then
            ' Exit Sub
            
    Else: DoCmd.RunSQL "INSERT INTO tblnewparts ( partno, xfile, issueno ) " _
        & "Values ('" & partno & "', '" & Forms!frmprojectstabbed.Form!xfile & "', '" & Forms!frmprojectstabbed.Form!issueno & "')"
    End If
End If
Cancel = True
Me.Undo
End Sub

 

[midiman69]

That works great.
Many thanks for your help Remou

Dave

 

[midiman69]

I have a problem with

Private Sub Field1_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblnewparts", "partno = '" & partno & "'") = 0 Then ...........

When using part number that contain characters such as / and - eg 35006/H or 041-03729A the code recognises these as "new parts" while in fact they are existing parts with matching records - Can any one help?

Also when using paste append to import data from excel all part numbers trigger the new part dialogue - why is this?

Dave

 

[Remou]

Please start a new thread for the above, I think it will ge you more answers. Thanks.