Dcount problem

M

midiman69

I have a problem with

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
Cancel = True
Me.Undo
Exit Sub

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

Cancel = True
Me.Undo


End If

End If

This works fine for part numbers such as 20025 but 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 - The partno field is text - 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?

Cheers Dave
 
K

Klatuu

I'm not sure this will resolve the issue, but you are not qualifying your
form controls and because the field name partno is the same as a text box on
your form partno, Access may be getting confused. Also, I would suggest you
use the DLookup rather than the DCount function. It will ususally be faster
because it stops a the first match, but DCount always scans the entire
database. Try this variation:
If IsNull(DLookup("[partno]", "tblnewparts", "[partno] = '" & Me.partno &
"'")) Then
If MsgBox("This is a New Part - Do You Wish To Add?", vbYesNo, _
"Project Costing Database") = vbNo Then
Cancel = True
Me.Undo
Exit Sub
Else
CurrentDb.Execute("INSERT INTO tblnewparts ( partno, xfile, issue )
" _
& "Values ('" & Me.partno & "', '" &
Forms!frmprojectstabbed.Form!xfile _
& "', '" & Forms!frmprojectstabbed.Form!issue & "');"),
dbFailOnError
End If

Also notice I replaced the RunSQL with CurrentDb.Execute. This method is
always much faster than the RunSQL. The Execute goes directly to jet where
RunSQL has to go through the Access UI. Inserting one record will only be
marginally faster; however, for bulk actions it is dramatically faster.
 
D

Dave

Thanks Klatuu/Richard

Apologies, I just realised I posted the wrong code, but managed to adapt
your suggestion

If IsNull(DLookup("[IZPN]", "tblpartsdatabase", "[IZPN] = '" & Me.partno &
"'")) Then
If MsgBox("This is a New Part - Do You Wish To Add?", vbYesNo, "Project
Costing Database") = vbNo Then
Cancel = True
Me.Undo
Exit Sub
Else: CurrentDb.Execute ("INSERT INTO tblnewparts ( partno, xfile,
issue )" & "Values('" & Me.partno & "', '" & Forms!frmprojectstabbed.Form! _
xfile & "', '" & Forms!frmprojectstabbed.Form!issue & "');"),
dbFailOnError
End If
End If

End Sub

One thing, the Me.undo doesn't work and I can't see why?
As you can proberly tell I am not very experienced with VBA - Thanks for
your help

Dave




Klatuu said:
I'm not sure this will resolve the issue, but you are not qualifying your
form controls and because the field name partno is the same as a text box
on
your form partno, Access may be getting confused. Also, I would suggest
you
use the DLookup rather than the DCount function. It will ususally be
faster
because it stops a the first match, but DCount always scans the entire
database. Try this variation:
If IsNull(DLookup("[partno]", "tblnewparts", "[partno] = '" & Me.partno &
"'")) Then
If MsgBox("This is a New Part - Do You Wish To Add?", vbYesNo, _
"Project Costing Database") = vbNo Then
Cancel = True
Me.Undo
Exit Sub
Else
CurrentDb.Execute("INSERT INTO tblnewparts ( partno, xfile, issue )
" _
& "Values ('" & Me.partno & "', '" &
Forms!frmprojectstabbed.Form!xfile _
& "', '" & Forms!frmprojectstabbed.Form!issue & "');"),
dbFailOnError
End If

Also notice I replaced the RunSQL with CurrentDb.Execute. This method is
always much faster than the RunSQL. The Execute goes directly to jet
where
RunSQL has to go through the Access UI. Inserting one record will only be
marginally faster; however, for bulk actions it is dramatically faster.
midiman69 said:
I have a problem with

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
Cancel = True
Me.Undo
Exit Sub

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

Cancel = True
Me.Undo


End If

End If

This works fine for part numbers such as 20025 but 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 - The partno field is text - 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?

Cheers Dave
Click to expand...
Click to expand...
 
D

Dave

Ahhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh

The cancel/undo was missing from the end of the code!
I'll never get the hang of VBA

Many thanks for your help

Dave


Dave said:
Thanks Klatuu/Richard

Apologies, I just realised I posted the wrong code, but managed to adapt
your suggestion

If IsNull(DLookup("[IZPN]", "tblpartsdatabase", "[IZPN] = '" & Me.partno &
"'")) Then
If MsgBox("This is a New Part - Do You Wish To Add?", vbYesNo, "Project
Costing Database") = vbNo Then
Cancel = True
Me.Undo
Exit Sub
Else: CurrentDb.Execute ("INSERT INTO tblnewparts ( partno, xfile,
issue )" & "Values('" & Me.partno & "', '" & Forms!frmprojectstabbed.Form!
_
xfile & "', '" & Forms!frmprojectstabbed.Form!issue & "');"),
dbFailOnError
End If
End If

End Sub

One thing, the Me.undo doesn't work and I can't see why?
As you can proberly tell I am not very experienced with VBA - Thanks for
your help

Dave




Klatuu said:
I'm not sure this will resolve the issue, but you are not qualifying your
form controls and because the field name partno is the same as a text box
on
your form partno, Access may be getting confused. Also, I would suggest
you
use the DLookup rather than the DCount function. It will ususally be
faster
because it stops a the first match, but DCount always scans the entire
database. Try this variation:
If IsNull(DLookup("[partno]", "tblnewparts", "[partno] = '" & Me.partno &
"'")) Then
If MsgBox("This is a New Part - Do You Wish To Add?", vbYesNo, _
"Project Costing Database") = vbNo Then
Cancel = True
Me.Undo
Exit Sub
Else
CurrentDb.Execute("INSERT INTO tblnewparts ( partno, xfile,
issue )
" _
& "Values ('" & Me.partno & "', '" &
Forms!frmprojectstabbed.Form!xfile _
& "', '" & Forms!frmprojectstabbed.Form!issue & "');"),
dbFailOnError
End If

Also notice I replaced the RunSQL with CurrentDb.Execute. This method is
always much faster than the RunSQL. The Execute goes directly to jet
where
RunSQL has to go through the Access UI. Inserting one record will only
be
marginally faster; however, for bulk actions it is dramatically faster.
midiman69 said:
I have a problem with

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
Cancel = True
Me.Undo
Exit Sub

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

Cancel = True
Me.Undo


End If

End If

This works fine for part numbers such as 20025 but 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 - The partno field is text - 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?

Cheers Dave
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

It the Me.Undo is not working, it may be that your form is not Dirty. That
is, no changes to the current record have been made.
Dave said:
Thanks Klatuu/Richard

Apologies, I just realised I posted the wrong code, but managed to adapt
your suggestion

If IsNull(DLookup("[IZPN]", "tblpartsdatabase", "[IZPN] = '" & Me.partno &
"'")) Then
If MsgBox("This is a New Part - Do You Wish To Add?", vbYesNo, "Project
Costing Database") = vbNo Then
Cancel = True
Me.Undo
Exit Sub
Else: CurrentDb.Execute ("INSERT INTO tblnewparts ( partno, xfile,
issue )" & "Values('" & Me.partno & "', '" & Forms!frmprojectstabbed.Form! _
xfile & "', '" & Forms!frmprojectstabbed.Form!issue & "');"),
dbFailOnError
End If
End If

End Sub

One thing, the Me.undo doesn't work and I can't see why?
As you can proberly tell I am not very experienced with VBA - Thanks for
your help

Dave




Klatuu said:
I'm not sure this will resolve the issue, but you are not qualifying your
form controls and because the field name partno is the same as a text box
on
your form partno, Access may be getting confused. Also, I would suggest
you
use the DLookup rather than the DCount function. It will ususally be
faster
because it stops a the first match, but DCount always scans the entire
database. Try this variation:
If IsNull(DLookup("[partno]", "tblnewparts", "[partno] = '" & Me.partno &
"'")) Then
If MsgBox("This is a New Part - Do You Wish To Add?", vbYesNo, _
"Project Costing Database") = vbNo Then
Cancel = True
Me.Undo
Exit Sub
Else
CurrentDb.Execute("INSERT INTO tblnewparts ( partno, xfile, issue )
" _
& "Values ('" & Me.partno & "', '" &
Forms!frmprojectstabbed.Form!xfile _
& "', '" & Forms!frmprojectstabbed.Form!issue & "');"),
dbFailOnError
End If

Also notice I replaced the RunSQL with CurrentDb.Execute. This method is
always much faster than the RunSQL. The Execute goes directly to jet
where
RunSQL has to go through the Access UI. Inserting one record will only be
marginally faster; however, for bulk actions it is dramatically faster.
midiman69 said:
I have a problem with

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
Cancel = True
Me.Undo
Exit Sub

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

Cancel = True
Me.Undo


End If

End If

This works fine for part numbers such as 20025 but 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 - The partno field is text - 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?

Cheers Dave
Click to expand...
Click to expand...
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
Run-time Error 3246 - Operation not supported in transactions 2
Data Validation - Help Required 1
Cancel = True "variable not defined" 3
Error 3075 Syntax Error 5
vendor enviromental database design 4
dropdown problem 2
Check for duplicates with multiple criteria 1

Top