Need to be able to update existing records from a form (1 Viewer)

mshome

New member
Local time
Today, 02:11
Joined
Dec 14, 2020
Messages
5
I have a table "Handheld_serials" that hold relevant inventory information pertaining to equipment. I have a form also tied to this table for entering in that information. I had managed to code in the LostFocus event to display a messsage box if it was a duplicate record so that you didn't get to the end of the form and get the duplicate primary key field message. Trying to save myself time. My main goal though is to have access check that if there is a duplicate, possibly on lostfocus or beforeupdate that instead of just warning me that there was a duplicate, to pull up the record in question and display it on the form so that it could be edited (such as the location where the device is). My field for lookup is Serial_Number (This is also my primary key field). I'm new to working with Access (haven't used it in almost 20 years), Help! I have googled, and bought two books on advanced access and I can only find a method to use an unbound combobox, which still isn't doing the desired effect.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:11
Joined
Feb 28, 2001
Messages
27,001
Not exactly clear what you are asking.

How would this duplicate number be entered? If manually, either that control's _Change event or _LostFocus event would be the place to detect a duplicate. Don't wait for the form's Before_Update or some other event. Look to a control event.

As to pulling up a specific record, try this as an experiment: Be sure that the form wizards are working, then create a combo box for which the primary field of interest is your Serial_Number. When you do this, the wizard gives you three options, one of which is to FIND THE RECORD associated with that number. Perhaps that would help. And there is nothing wrong with having your serial number appear twice on the same form if one of them is a "find the record" combo box, because the combo box has its own .RowSource that is independent of the form's .RecordSource - i.e. they don't overlap.

Does this sound like what you were seeking? Or did I just confuse matters more?
 

mshome

New member
Local time
Today, 02:11
Joined
Dec 14, 2020
Messages
5
I created a combo box to find the record, based on the serial number but it doesn't display in the form fields. I want to pull the duplicate record in so it can be edited, since the location can change based on needs of that location. Otherwise we won't be editing data, but even in the serial number field on the form, if it find a duplicate it acts more like it's trying to create a new record instead of updating what's there. Is there something in the form or table I would need to change to make that work? But of course I need it to display the record before it matters if it's trying to add instead of update.
 

Attachments

  • Capture.PNG
    Capture.PNG
    14.3 KB · Views: 86

mshome

New member
Local time
Today, 02:11
Joined
Dec 14, 2020
Messages
5
I had also tried to have it so you could search through all the records by using the navigation at the bottom of the access form, but it only shows what's been added in that particular session. This was a workaround I was trying while trying to figure out what I really wanted it to do. I tried the FindRecord method with the combo box initially by typing the code in manually as an event procedure, but it never liked the syntax, even though I copied an example in a book I have, while changing out for my field/table names.
 

mshome

New member
Local time
Today, 02:11
Joined
Dec 14, 2020
Messages
5
Nevermind. Found another thread that was just similar enough that from that person's replies I figured out my issue and now it works.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:11
Joined
Oct 29, 2018
Messages
21,358
Nevermind. Found another thread that was just similar enough that from that person's replies I figured out my issue and now it works.
Hi. Welcome to AWF!

Glad to hear you got it sorted out. You might also consider posting a link to the thread you found; or better yet, perhaps post the solution you ended up using, in case it helps somebody else in the future.

Cheers!
 

mshome

New member
Local time
Today, 02:11
Joined
Dec 14, 2020
Messages
5
Yeah, although now I'm trying to do the exact same thing on another form where Serial_Number isn't the key, there's an autonumber field as the key since this form is for logging device repairs and we often see the same device multiple times, though it hasn't come up since I created the database. When I pick the serial number from the combo box, it shows a different record and I can't figure out why.
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    11 KB · Views: 88
  • Capture2.PNG
    Capture2.PNG
    5.1 KB · Views: 103
  • Capture3.PNG
    Capture3.PNG
    8.6 KB · Views: 112

Users who are viewing this thread

Top Bottom