It record exists then...

[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

 

[Minton M]

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

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

 

[Design by 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

 

[Minton M]

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

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

 

[Design by Sue]

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

Sue

 

[Minton M]

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

Sue

Sorry, another type - should be If NOT (not nor, which is a pre-
caffeine amateur error).

 

[Linq Adams via AccessMonster.com]

A single line of code using DCount() will perform this task.

 

[Design by Sue]

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

 

[Design by Sue]

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

thanks
Sue

 

[geppo]

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

 

[geppo]

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

 

[Design by Sue]

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

 

[Minton M]

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

Indeed, DCount can be used but I like using VBA because it makes
debugging easier. The cat-skinning methods just increased by one!
Hurrah!

 

[Design by Sue]

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

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

Indeed, DCount can be used but I like using VBA because it makes
debugging easier. The cat-skinning methods just increased by one!
Hurrah!

 

[Minton M]

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

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

Indeed, DCount can be used but I like using VBA because it makes
debugging easier. The cat-skinning methods just increased by one!
Hurrah!

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.

 

[geppo]

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= "&
me.NameYourControlPartNumber)

if not rs.eof then

 

[geppo]

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.

To say the truth seems me more' easy to make your solution understand him.

 

[Minton M]

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

if not rs.eof then

Geppo is a scholar and a gentleman, wielding his legally-registered
copy of Access as a force for good.

 

[Design by Sue]

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

 

[geppo]

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

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