Search for existing data, add new record if not found.

[AntelopeOne]

Greetings all,



I'm just getting started with Access and trying to build a form that will allow us to scan a unique serial number off of a product bar code. If the serial number already exists, it would locate that record and populate the form with the existing data so that it can be modified. If the serial number doesn't exist, it would add a new row. I have to assume this is possible, but haven't found anything in the text I own (The Missing Manual).

Thanks if you're able to push me in the right direction!

[ranman256]

If you key the serial #in the table, then

scan the serial (to txtSerial in form)
append it (run append query that adds it to the table...since keyed, it wont duplicate) run an append qry based on the txtSerial
open the record. docmd.openquery "qsSerial"

[AntelopeOne]

Thanks but that didn't get me any further than I already am...maybe I have more homework to do than I thought.

The serial number is already my key field, but Access doesn't complain about duplicates until I try to commit the record by clicking the 'next record' button.

My life for a decent Access manual...

[orange]

If your serial number is designated as your Primary Key, then Access will not allow a duplicate serial number.

[Missinglinq]

Everyone note that this is also posted here

http://www.dbforums.com/showthread.p...ew-add-new-row

Linq ;0)>

[June7]

Could try scanning into a combobox and take advantage of its NotInList event.

Otherwise, code in textbox AfterUpdate event would search for the value in table and act accordingly to what is discovered.

With either control, must be UNBOUND. Controls used to input search criteria must be UNBOUND.

Use bookmarks and recordsetclone to find record without filtering or can apply criteria to the form's Filter property.

If IsNull(DLookup("barcode", "tablename", "barcode='" & Me.controlname & "'")) Then
'code for new record, maybe just move to new record row and set value of barcode field to the value from search control
Else
'code to find and move to existing record or apply filter
End If

[Missinglinq]

...The serial number is already my key field...

I asked about this in the other post, and since it appears to be true:

Create an Unbound Textbox in your Form Header, as June7 suggested, and name it txtSerialSearch. Then use this code:

Private Sub txtSerialSearch_AfterUpdate()

 Dim rst As Recordset

 Set rst = Me.RecordsetClone

 rst.FindFirst "[SerialNum] = '" & Me.txtSerialSearch & "'"
 
  If Not rst.NoMatch Then
      Me.Bookmark = rst.Bookmark
   Else
    DoCmd.GoToRecord , , acNewRec
    Me.SerialNum = Me.txtSerialSearch
   End If

rst.Close

Set rst = Nothing

End Sub

This assumes that SerialNum is defined as Text. I and others generally define a Field like this as Text, even if it only contains digits, unless it is going to be used for math operations. But if you want it to be defined as a Number, replace

rst.FindFirst "[SerialNum] = '" & Me.txtSerialSearch & "'"

with

rst.FindFirst "[SerialNum] = " & Me.txtSerialSearch

Linq ;0)>

[AntelopeOne]

Thanks, I'll give that a try.

As for your highlighting my post on another forum, are the two sites related somehow?

[Missinglinq]

No, but many members, here, like myself, contribute to more than one forum. Double-posting is frowned on by members of most forums and actually forbidden by a couple of them. The problems associated with Double-posting:

1. A member of ForumA may waste their time posting advice that has already been posted in ForumB
2. A member of ForumA may waste their time posting advice that has already been posted in ForumB but has not resolved the issue
3. You may respond to a request for more information on ForumA and this info won't be available to members on ForumB

Members of this and other Access forums are professionals who are volunteering their valuable time and knowledge, to help other developers, and wasting their time is simply not cool, as my generation would say!

If you feel that you absolutely must Double-post, for instance if you haven't received any replies to your original thread, forum etiquette dictates that you at least inform people that this has been done so that they may view what advice has already been given.

We understand that you are new to all of this, and this isn't meant be censorial, but merely informative.

BTW, Welcome to AccessForums.Net!

Linq ;0)>

[AntelopeOne]

Gotcha! My apologies, I was simply trying to cast as wide a net as possible, lesson learned.

So...the code you provided is working well, as expected, thank you again for taking the time.

I still have a few things to work out in terms of auto-tabbing, and maybe Google can help me now that I'm a bit further.

I do have one more question for now, if you would be so kind.

As it stands, when I scan a serial that is a new record, it autotabs to the ID field, and if I scan the serial again, it populates the ID field (autonumber), and the focus moves to the PartNum field, which will also be scanned in from the label. So that will work as is...






As I'm trying to streamline things as much as possible for the end users, ideally, once the label was scanned and the focus moved into the data fields, the text in the search field would clear. Or, upon committing the data to the table, the focus would return to the search field so that another scan would replace the existing serial. I'm assuming I just need one line of code to add to the end of your example to clear the existing serial, but everything I have tried throws an error. Returning focus to the search field is probably more involved, but maybe a better solution?

Thank you again, good sir, and thank you for the welcome

[June7]

What have you tried? The following should work:

Me.txtSerialSearch = Null

[AntelopeOne]

I was trying "= Nothing", thank you!