Field used to match existing record in tbl causing big problems

[RobertIngles]

I am trying to fix a form used to book laptops for upgrades. My form has a field called "Barcode" which is the primary index in a table named "TBLLaptop". The user fills in the Barcode field, clicks "Confirm Barcode" button. If the Barcode does not exist in the table an add new record form opens, if the barcode exists the cursor moves to the next field which is a combo box to allow selection of a location and date for the upgrades to be performed.

Here's the problem, if the barcode exists and the user completes the form, clicks the close form button I get an error saying that I cannot save the record as it would create a duplicate record.

The form is based on a query of two tables joined by field "UserID" - tables are TBLLaptop and TBLUser. I am not trying to create a new laptop record, I just want to update the BookingID field to reference the date and location for the upgrade.

I really thought I had this form well designed but I seemed to have screwed it up big time. I only just tonight made the LaptopID the primary key - the primary key was preiously the standard ID number Access assigns. I now fear that for the last two days the users have been creating duplicate records in TBLLaptop - can't say for sure as I don't have access to the network outside of the office. Can anyone tell me what I have done wrong?

If you need more info in order to help me, please tell me what you need to know. I will post the additional info as soon as I read your reply.

Thanks
Robert

 

[MajP]

To make this easier you really should seperate data input from data navigation. My guess is that your barcode combo is a bound control. If you want to use it for navigation it cannot be bound because you are likely creating a new record. Can you provide your code on the combo? Is the control bound? Is the form bound?
My personnel design preference is always to seperate data entry from data display. So I might have a form that shows a bunch of information in continous form view with lots of features for sorting and filtering. But it is read only. Once I navigate to a record then I can click on it to edit or get the details in single form view. If I do not find the record then open a new one in single form add mode.

 

[RobertIngles]

Thanks MajP
I have created a new form that will display information only from TBLLaptop and TBLUser. I then plan to create a subform for bookings with TBLLaptop "[UserID]" as the join - one user to many laptops.

Before I add the subform I need to solve a problem with the main form.

The first field the user fills in is [Laptop]ID. Click the confirm button and if the [LaptopID} already exists then auto populate the related fields on the main form with the rest of the details. I have exactly the same button for the [UserID] and it works fine to fill in the vacant fields with the user information but I still get the "you are trying to add a duplicate record you idiot" error message

I have a feeling that this has something to do with the AllowAdditions property being set to yes but if I set it to know it opens a blank screen.

How do I set this form up to simply show all of the Laptop and User details already stored in the related tables instead of opening a create new record which will cause duplicates?

I know I can do it by utilizing a query with a user entered parameter but I have been asked to cut down the work flow by the users and need the form open so that if the laptopID or UserID does not exist, an add Laptop Profile and/or UserProfile form opens and then pushes the data to the active main booking form - They would then be able to book the appointment via the subform.

Apparently this cannot be done with the user parameter query method so it would mean leaving the booking form opening and completing the AddNewLaptopProfile form, opening and completing the AddNewUserProfile and then going back to the booking form to finish the job.

Does any of this make sense?