Display record if unique identifier already exists 1

[Nilo]

I have a field named Pcode within a table and on a form and I have a textbox named ProducerCode that has Pcode as a controlsource.

The form is generally a data entry form but I need to create it so that if somebody enters a value in the ProducerCode textbox that already exists in the table, it will display that record that has that specific value in the Pcode field.

If the value does not exist, then continue adding the new entry.

Since the value in the Pcode field is basically my unique identifier, that value can only be in the table once.

Any help would be greatly appreciated
Thanks in advance

 

[Maquis]

Here is some code that should get you on the right track. Place the code in the lost focus event of your Primary Key textbox. You'll need to tweak it to fit your fields.

Dim TableName As String
Dim Criteria as String
Dim Message as string
Dim NumRecords as Long
Dim rs As Recordset

Set rs = Me.RecordsetClone
TableName = &quot;<yourtablename>&quot;
Criteria = &quot;[Pcode] = &quot; Me!ProducerCode
NumRecords = DCount(&quot;Pcode&quot;, TableName, Criteria)
If NumRecords > 0 Then
   Message = &quot;Error! That Pcode already exists in the table, Cannot Add!&quot;
   MsgBox Message, vbCritical
   DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
   rs.FindFirst Criteria
   Me.Bookmark = rs.Bookmark
   rs.Close
   Set rs = Nothing
   Exit Sub
End If

Maq
<insert witty signature here>

 

[Nilo]

Thanks Maquis,
That works great.
I actually have been toying around with a bunch of different methods but I think I'm missing a big piece of know how.

This is what I need to have happen:
(and I'll be the first to admit I might be asking too much)

when I go to add a new record, the first field I enter information in is this ProducerCode Textbox I was talking about.

What I need to have happen is (and I've been putting all my code in the After Update property)have the ProducerCode textbox perform a search and if the search confirms that this ProducerCode value has already been entered previously, display the record in which it belongs.

And if it doesn't exist, continue adding the new record.

Right now, I could care a less about any of the 'nice to haves'.
If anybody knows how to find a record from a field value and display it in the current form that would be great.

I'm using this VB 4.0 book, and I thought I found a way to do it, but it just isn't working in VBA(Access97).

Thanks

 

[Maquis]

Nilo, the code I posted earlier will do exactly what you describe. But, you need to put the code in the before update or lost focus event of your Pcode textbox. I think after update will be too late. (I could be wrong about this, though)

The rs.findfirst command will move your form to the correct record. Maq
<insert witty signature here>

 

[Nilo]

Hi Maq,
The code was getting hung up on:
Criteria = &quot;[Pcode] = &quot; Me!ProducerCode

so I changed it to:
Criteria = &quot;[Pcode]=&quot; & &quot;'&quot; & Me![ProducerCode] & &quot;'&quot;

but now it's stopping at:
Me.Bookmark = rs.Bookmark

Since the code has never made it all the way to the end yet, I'm not sure if the problem was there front he beginning or if I messed things up.

I'm new at this so troubleshooting isn't my strong point yet

 

[Maquis]

Well, like I said, the code will definitely need tweaking to fit your situation. What error are you receiving?

Be sure to double check all your field names. The problem may be something as simple as you typing ProducerCode for the name when the field name is actually called ProducerCd.

Also, what version of Access do you have? I have Access 97 and this code runs fine in my database, but you'll probably need to set some dao references if you are running Access 2000 or later. (Sorry, I don't know the complete syntax since I've never used 2000) Maq
<insert witty signature here>

 

[Nilo]

Thank you for getting back to me so quickly on this.
I have 97 so we're using the same version.

The error I am receiving is :
Run-time error '2105'

It says I can't go to the specified record.
and then it says, 'you may be at the end of a recordset'
When I go to Debug it highlights the line referencing the bookmark.
Me.Bookmark = rs.Bookmark

 

[Maquis]

Ok, I confess. I didn't write the code, I stole it from a co-worker's database and cut and pasted it into my own database where it's been working happily for years. I guess I really should have investigated what it actually did before posting it here. Sorry 'bout that.

Excuse me for a sec while I play around in my database and find out exactly how this thing works.
---> ---> ---> ---> --->




Ok, I'm back, and here's what I've learned. The code will work as posted from the lost focus event, however this really isn't the best way to do it. You should put the code in the before update event (where it promptly bombs ). To get it working, simply change the hideous looking &quot;DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70&quot; command to &quot;me.undo&quot;.

Whew!
Maq
<insert witty signature here>

 

[Nilo]

YOU ROCK!

Works like charm!

Thank you so much!

OK,now I can stop bothering you
Have a good weekend