It record exists then...

  • Thread starter Thread starter Design by Sue
  • Start date Start date
D

Design by Sue

I would like to create a form for inputing new records. The first field is
PartNumber. If this PartNumber already exists in the InfoTbl (in the field
PartNumber) I then want a message box to appear that the number is already in
the database. If there the number does not exist, the user should be allowed
to continue with the data entry. I have searched the files here and find
reference to FindFirst and NoMatch but I can't figure out how to write the
code for my needs. Please help.

thanks
Sue
 
I would like to create a form for inputing new records. The first field is
PartNumber. If this PartNumber already exists in the InfoTbl (in the field
PartNumber) I then want a message box to appear that the number is already in
the database. If there the number does not exist, the user should be allowed
to continue with the data entry. I have searched the files here and find
reference to FindFirst and NoMatch but I can't figure out how to write the
code for my needs. Please help.

thanks
Sue
Click to expand...

In the BeforeUpdate event, run this query:

dim rs as recordset, db as database
set db = currentdb

set rs.openrecordset("select count(*) from InfoTbl where PartNumber=
(the value of the textbox)")
if nor rs.eof
If rs(0)>0 then
msgbox "Part number exists."
Cancel=TRUE
end if
end if
rs.close
set rs= nothing

Code is untested but looks reasonable enough!

Hope this helps,
James
 
I tried that and the debugger stops on the line set rs.openrecordset... and
says an = is requried or there is a syntax error. I am not very good at this
- what in your code was I to change to make it work with my database if
anything.

Thanks
Sue
 
I tried that and the debugger stops on the line set rs.openrecordset... and
says an = is requried or there is a syntax error. I am not very good at this
- what in your code was I to change to make it work with my database if
anything.

Thanks
Sue
Click to expand...

Sorry, my typo... should read:

- Set rs= db.openrecordset...

Additionally, the part that says "the value of the textbox" you need
to put the textbox name.value instead of my bracketed comments.

-- James
 
Making progress - I hope - now it is stopping on "if nor rs.eof" with
Expected Then or Go To.

Sue
 
Making progress - I hope - now it is stopping on "if nor rs.eof" with
Expected Then or Go To.

Sue
Click to expand...

Sorry, another type - should be If NOT (not nor, which is a pre-
caffeine amateur error).
 
A single line of code using DCount() will perform this task.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
 
This is what I have

Dim rs As Recordset, db As Database
Set db = CurrentDb

Set rs = db.OpenRecordset("select count(*) from InfoTbl where
PartNumber=(PartNumberTxt)")if not rs.eof
If rs(0) > 0 Then
MsgBox "Part number exists."
Cancel = True
End If
End If
rs.Close
Set rs = Nothing

With everything from Set rs=db.. through if not rs.eof is on one line
(pasting it here puts in on several) With it on one line I get "Compile
Error: Syntax Error.

If I put the If Not rs.eof on a separate line I get the Expected Then or Go
to.

I am so sorry to be so dense - I should be able to see what to do, but I
can't get it

Thanks for all of your help

Sue
 
Thanks - but can you help me with that code - as you can see I am not good at
this!

thanks
Sue
 
Ciao Design by Sue wrote:
---cut---

it tries this way:

If Not IsNull(DLookup("[PartNumber]", "InfoTbl", _
"[PartNumber]=" & Chr$(34) & Me.NameYourTextbox.Value & Chr$(34))) _
And Me.NameYourTextbox.Value <> Nz(NameYourTextbox.Value.OldValue) Then
Cancel = True
MsgBox "Part number exists."
Me.NameYourTextbox.Undo
End If

this code I have found him on this Italian site:

http://www.donkarl.com/it/FAQ/FAQ4Maschere.htm#4.25


me not parlo/leggo the English use a translator, therefore excuse me for the
errors
 
Sorry, if PartNumber is numerical you/he/she must be written this way:

If Not IsNull(DLookup("[PartNumber]", "InfoTbl", _
"[PartNumber]=" & Me.NameYourTextbox.Value)) _
And Me.NameYourTextbox.Value <>
Nz(NameYourTextbox.Value.OldValue) Then Cancel = True
MsgBox "Part number exists."
Me.NameYourTextbox.Undo
End If
 
Runtime Error 424: Object required is the error I get with this.

I appreciate all of the help, but this is really frustrating - this should
be so much easier than it is - this seems like a common need.

Sue
 
Sorry, if PartNumber is numerical you/he/she must be written this way:

If Not IsNull(DLookup("[PartNumber]", "InfoTbl", _
"[PartNumber]=" & Me.NameYourTextbox.Value)) _
And Me.NameYourTextbox.Value <>
Nz(NameYourTextbox.Value.OldValue) Then Cancel = True
MsgBox "Part number exists."
Me.NameYourTextbox.Undo
End If
Click to expand...

Indeed, DCount can be used but I like using VBA because it makes
debugging easier. The cat-skinning methods just increased by one!
Hurrah!
 
As per post above James, I still can't make it work. Post repeated below:

This is what I have

Dim rs As Recordset, db As Database
Set db = CurrentDb

Set rs = db.OpenRecordset("select count(*) from InfoTbl where
PartNumber=(PartNumberTxt)")if not rs.eof
If rs(0) > 0 Then
MsgBox "Part number exists."
Cancel = True
End If
End If
rs.Close
Set rs = Nothing

With everything from Set rs=db.. through if not rs.eof is on one line
(pasting it here puts in on several) With it on one line I get "Compile
Error: Syntax Error.

If I put the If Not rs.eof on a separate line I get the Expected Then or Go
to.

I am so sorry to be so dense - I should be able to see what to do, but I
can't get it

Thanks for all of your help

Sue


Minton M said:
Sorry, if PartNumber is numerical you/he/she must be written this way:

If Not IsNull(DLookup("[PartNumber]", "InfoTbl", _
"[PartNumber]=" & Me.NameYourTextbox.Value)) _
And Me.NameYourTextbox.Value <>
Nz(NameYourTextbox.Value.OldValue) Then Cancel = True
MsgBox "Part number exists."
Me.NameYourTextbox.Undo
End If
Click to expand...

Indeed, DCount can be used but I like using VBA because it makes
debugging easier. The cat-skinning methods just increased by one!
Hurrah!
Click to expand...
 
As per post above James, I still can't make it work. Post repeated below:

This is what I have

Dim rs As Recordset, db As Database
Set db = CurrentDb

Set rs = db.OpenRecordset("select count(*) from InfoTbl where
PartNumber=(PartNumberTxt)")if not rs.eof
If rs(0) > 0 Then
MsgBox "Part number exists."
Cancel = True
End If
End If
rs.Close
Set rs = Nothing

With everything from Set rs=db.. through if not rs.eof is on one line
(pasting it here puts in on several) With it on one line I get "Compile
Error: Syntax Error.

If I put the If Not rs.eof on a separate line I get the Expected Then or Go
to.

I am so sorry to be so dense - I should be able to see what to do, but I
can't get it

Thanks for all of your help

Sue

Minton M said:
Sorry, if PartNumber is numerical you/he/she must be written this way:
If Not IsNull(DLookup("[PartNumber]", "InfoTbl", _
"[PartNumber]=" & Me.NameYourTextbox.Value)) _
And Me.NameYourTextbox.Value <>
Nz(NameYourTextbox.Value.OldValue) Then Cancel = True
MsgBox "Part number exists."
Me.NameYourTextbox.Undo
End If
Click to expand...
Click to expand...
Indeed, DCount can be used but I like using VBA because it makes
debugging easier. The cat-skinning methods just increased by one!
Hurrah!
Click to expand...
Click to expand...

You have to replace "(PartNumberTxt)" with the value you're comparing
against. To be honest, I'm having one of those days where I'm giving
out overly elaborate solutions - I think Geppo's DCount solution is
the way to go for simplicity here.

Jesus, I'm sure it's some side-effect of all the booze I drank at
Christmas.
 
Ciao said:
As per post above James, I still can't make it work. Post repeated
below:

This is what I have

Dim rs As Recordset, db As Database
Set db = CurrentDb
Click to expand...

Set rs = db.OpenRecordset("select count(*) from InfoTbl where PartNumber= "&
me.NameYourControlPartNumber)

if not rs.eof then
 
Ciao Minton M wrote:
--cut--
You have to replace "(PartNumberTxt)" with the value you're comparing
against. To be honest, I'm having one of those days where I'm giving
out overly elaborate solutions - I think Geppo's DCount solution is
the way to go for simplicity here.

Jesus, I'm sure it's some side-effect of all the booze I drank at
Christmas.
Click to expand...

To say the truth seems me more' easy to make your solution understand him.
:-)
 
Set rs = db.OpenRecordset("select count(*) from InfoTbl where PartNumber= "&
me.NameYourControlPartNumber)

if not rs.eof then
Click to expand...

Geppo is a scholar and a gentleman, wielding his legally-registered
copy of Access as a force for good.
 
Thank you to all - I finally found a friend who wrote the following for me
and it is doing exactly what I need.

If DLookup("[PartNumber]", "PartSuffixTbl", "PartNumber = " &
Me.PartNumberTxt) Then

MsgBox "That Wheel Number already exists."

PartNumberTxt.SetFocus
 
Ciao said:
Runtime Error 424: Object required is the error I get with this.

I appreciate all of the help, but this is really frustrating - this
should be so much easier than it is - this seems like a common need.

Sue
Click to expand...

This way it works:

If Not IsNull(DLookup("[PartNumber]", "InfoTbl", _
"[PartNumber]=" & str(Me.NameYourTextbox.Value))) _
And Me.NameYourTextbox.Value <> Nz(NameYourTextbox.OldValue) Then

Cancel = True
MsgBox "Part number exists."
Me.NameYourTextbox.Undo
End If
 
Back
Top