Insert Into Problem

G

Guest

I am trying to insert a record in to tblnewparts if a field contains no
matching records using the following

If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then

DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & "SELECT " & partno & ";"


End If

I gety no error message and the code complies but it doesn't work - can any
one please help?

Dave
 
G

Guest

Why do you need this DLookUp, what are you trying to return?

If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then

When do you assign value to the partno, that you are using in the Insert?


If the partno is a value that suppose to return using the dlookup, then use
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"

If the partno is string then use
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"
 
G

Guest

Hi Ofer,

Thanks for the reply.

The DlookUp is to check if a matching record exists, if not add the "new"
part number to tblnewparts - I have tried both of your suggestions - still
not working.
I have tried using the Before Update and After Update events.
I am very inexperienced in VBA so I hope you will forgive me if this is some
thing easy.

Dave

Ofer Cohen said:
Why do you need this DLookUp, what are you trying to return?

If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then

When do you assign value to the partno, that you are using in the Insert?


If the partno is a value that suppose to return using the dlookup, then use
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"

If the partno is string then use
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"

--
Good Luck
BS"D


midiman69 said:
I am trying to insert a record in to tblnewparts if a field contains no
matching records using the following

If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then

DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & "SELECT " & partno & ";"


End If

I gety no error message and the code complies but it doesn't work - can any
one please help?

Dave
Click to expand...
Click to expand...
 
G

Guest

Do you get an error message? and if so, what is it?

I think you are looking for something like

' Check if record exist using the dcount
If DCount("*", "qrybom", "partno = " & partno)=0 Then
' if it doesn't exist enter a new value to the table
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"
End If


--
Good Luck
BS"D


midiman69 said:
Hi Ofer,

Thanks for the reply.

The DlookUp is to check if a matching record exists, if not add the "new"
part number to tblnewparts - I have tried both of your suggestions - still
not working.
I have tried using the Before Update and After Update events.
I am very inexperienced in VBA so I hope you will forgive me if this is some
thing easy.

Dave

Ofer Cohen said:
Why do you need this DLookUp, what are you trying to return?

If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then

When do you assign value to the partno, that you are using in the Insert?


If the partno is a value that suppose to return using the dlookup, then use
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"

If the partno is string then use
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"

--
Good Luck
BS"D


midiman69 said:
I am trying to insert a record in to tblnewparts if a field contains no
matching records using the following

If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then

DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & "SELECT " & partno & ";"


End If

I gety no error message and the code complies but it doesn't work - can any
one please help?

Dave
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Ofer

That is exactly what I am trying to do - No Error message with the first
code but you last generates "Data type mismatch in Criteria Expression"
partno is text.

Dave

Ofer Cohen said:
Do you get an error message? and if so, what is it?

I think you are looking for something like

' Check if record exist using the dcount
If DCount("*", "qrybom", "partno = " & partno)=0 Then
' if it doesn't exist enter a new value to the table
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"
End If


--
Good Luck
BS"D


midiman69 said:
Hi Ofer,

Thanks for the reply.

The DlookUp is to check if a matching record exists, if not add the "new"
part number to tblnewparts - I have tried both of your suggestions - still
not working.
I have tried using the Before Update and After Update events.
I am very inexperienced in VBA so I hope you will forgive me if this is some
thing easy.

Dave

Ofer Cohen said:
Why do you need this DLookUp, what are you trying to return?

If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then

When do you assign value to the partno, that you are using in the Insert?


If the partno is a value that suppose to return using the dlookup, then use
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"

If the partno is string then use
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"

--
Good Luck
BS"D


:

I am trying to insert a record in to tblnewparts if a field contains no
matching records using the following

If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then

DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & "SELECT " & partno & ";"


End If

I gety no error message and the code complies but it doesn't work - can any
one please help?

Dave
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

In that case, if the partno is a text type field, try

If DCount("*", "qrybom", "partno = '" & partno & "'")=0 Then
' if it doesn't exist enter a new value to the table
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"
End If


--
Good Luck
BS"D


midiman69 said:
Hi Ofer

That is exactly what I am trying to do - No Error message with the first
code but you last generates "Data type mismatch in Criteria Expression"
partno is text.

Dave

Ofer Cohen said:
Do you get an error message? and if so, what is it?

I think you are looking for something like

' Check if record exist using the dcount
If DCount("*", "qrybom", "partno = " & partno)=0 Then
' if it doesn't exist enter a new value to the table
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"
End If


--
Good Luck
BS"D


midiman69 said:
Hi Ofer,

Thanks for the reply.

The DlookUp is to check if a matching record exists, if not add the "new"
part number to tblnewparts - I have tried both of your suggestions - still
not working.
I have tried using the Before Update and After Update events.
I am very inexperienced in VBA so I hope you will forgive me if this is some
thing easy.

Dave

:

Why do you need this DLookUp, what are you trying to return?

If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then

When do you assign value to the partno, that you are using in the Insert?


If the partno is a value that suppose to return using the dlookup, then use
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"

If the partno is string then use
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"

--
Good Luck
BS"D


:

I am trying to insert a record in to tblnewparts if a field contains no
matching records using the following

If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then

DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & "SELECT " & partno & ";"


End If

I gety no error message and the code complies but it doesn't work - can any
one please help?

Dave
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Ofer,

Many thanks that works fine - how do I combine this with a message box?
I am trying

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

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

End If

Again this doesn't work (your code without the msgbox does) Also, how to I
clear the "new part" data from the lookup field?

Thanks for all your help - I'll never get the hang of this VBA!!

Dave



End Sub

Ofer Cohen said:
In that case, if the partno is a text type field, try

If DCount("*", "qrybom", "partno = '" & partno & "'")=0 Then
' if it doesn't exist enter a new value to the table
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"
End If


--
Good Luck
BS"D


midiman69 said:
Hi Ofer

That is exactly what I am trying to do - No Error message with the first
code but you last generates "Data type mismatch in Criteria Expression"
partno is text.

Dave

Ofer Cohen said:
Do you get an error message? and if so, what is it?

I think you are looking for something like

' Check if record exist using the dcount
If DCount("*", "qrybom", "partno = " & partno)=0 Then
' if it doesn't exist enter a new value to the table
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"
End If


--
Good Luck
BS"D


:

Hi Ofer,

Thanks for the reply.

The DlookUp is to check if a matching record exists, if not add the "new"
part number to tblnewparts - I have tried both of your suggestions - still
not working.
I have tried using the Before Update and After Update events.
I am very inexperienced in VBA so I hope you will forgive me if this is some
thing easy.

Dave

:

Why do you need this DLookUp, what are you trying to return?

If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then

When do you assign value to the partno, that you are using in the Insert?


If the partno is a value that suppose to return using the dlookup, then use
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"

If the partno is string then use
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"

--
Good Luck
BS"D


:

I am trying to insert a record in to tblnewparts if a field contains no
matching records using the following

If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then

DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & "SELECT " & partno & ";"


End If

I gety no error message and the code complies but it doesn't work - can any
one please help?

Dave
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Try this

If DCount("*", "qrybom", "partno = '" & partno & "'") = 0 Then
If MsgBox("This is a New Part - Do You Wish To Add?", vbYesNo, "Project
Costing Database") = vbYes Then
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno
& "')"
End If
End If

=================
To clear the text box use
Me.partno = ""

--
Good Luck
BS"D


midiman69 said:
Hi Ofer,

Many thanks that works fine - how do I combine this with a message box?
I am trying

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

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

End If

Again this doesn't work (your code without the msgbox does) Also, how to I
clear the "new part" data from the lookup field?

Thanks for all your help - I'll never get the hang of this VBA!!

Dave



End Sub

Ofer Cohen said:
In that case, if the partno is a text type field, try

If DCount("*", "qrybom", "partno = '" & partno & "'")=0 Then
' if it doesn't exist enter a new value to the table
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"
End If


--
Good Luck
BS"D


midiman69 said:
Hi Ofer

That is exactly what I am trying to do - No Error message with the first
code but you last generates "Data type mismatch in Criteria Expression"
partno is text.

Dave

:

Do you get an error message? and if so, what is it?

I think you are looking for something like

' Check if record exist using the dcount
If DCount("*", "qrybom", "partno = " & partno)=0 Then
' if it doesn't exist enter a new value to the table
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"
End If


--
Good Luck
BS"D


:

Hi Ofer,

Thanks for the reply.

The DlookUp is to check if a matching record exists, if not add the "new"
part number to tblnewparts - I have tried both of your suggestions - still
not working.
I have tried using the Before Update and After Update events.
I am very inexperienced in VBA so I hope you will forgive me if this is some
thing easy.

Dave

:

Why do you need this DLookUp, what are you trying to return?

If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then

When do you assign value to the partno, that you are using in the Insert?


If the partno is a value that suppose to return using the dlookup, then use
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"

If the partno is string then use
DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"

--
Good Luck
BS"D


:

I am trying to insert a record in to tblnewparts if a field contains no
matching records using the following

If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then

DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & "SELECT " & partno & ";"


End If

I gety no error message and the code complies but it doesn't work - can any
one please help?

Dave
Click to expand...
Click to expand...
Click to expand...
 
B

BD

Hi Ofer,

Many thanks that works fine - how do I combine this with a message box?
I am trying

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

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

End If

Again this doesn't work (your code without the msgbox does) Also, how to I
clear the "new part" data from the lookup field?

Thanks for all your help - I'll never get the hang of this VBA!!

Dave
Click to expand...



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

[]'s
BD
 
G

Guest

Many thanks for you help guys - very much appreciated.

One more thing, the subform is bound to the main form by two primary keys
Xfile and issno- how would I modify the code to include these so that the
"New part" is bound to the main form? is this the "WHERE statement?

Any further help would be brilliant

Dave

BD said:
Hi Ofer,

Many thanks that works fine - how do I combine this with a message box?
I am trying

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

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

End If

Again this doesn't work (your code without the msgbox does) Also, how to I
clear the "new part" data from the lookup field?

Thanks for all your help - I'll never get the hang of this VBA!!

Dave
Click to expand...



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

[]'s
BD
Click to expand...
 
G

Guest

Many thanks for you help guys - very much appreciated.

One more thing, the subform is bound to the main form by two primary keys
Xfile and issno- how would I modify the code to include these so that the
"New part" is bound to the main form? is this the "WHERE statement?

Any further help would be brilliant

Dave

BD said:
Hi Ofer,

Many thanks that works fine - how do I combine this with a message box?
I am trying

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

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

End If

Again this doesn't work (your code without the msgbox does) Also, how to I
clear the "new part" data from the lookup field?

Thanks for all your help - I'll never get the hang of this VBA!!

Dave
Click to expand...



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

[]'s
BD
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dcount problem when using text characters? 1
Dcount problem 5
Dcount Problem - Can any one help? 0
Data Validation - Help Required 1
Run-time Error 3246 - Operation not supported in transactions 2
DLookup Problem 4
QueryToJoineMultipleTables 2
Designing Queries using SQL 3

Top