Populate form fields if record exists in table

[RobertIngles]

I know this is a dumb one but I can't find it in my refernce books

I have a form that the user enters a alphanumeric value into the first field. They click a control button - if the value does not exist in the corresponding table a popup prompts them to create a new record (works perfectly thanks to help from this forum).

If the value does match a corresponding record in the table when they click, I want to automatically fill in the remainder of the vacant fields in the active form and move the cursor to the next field. The form is based on a query ("QRYNewBookingV2") made up of two joined tables - 'TBLLaptop' and 'TBLUser' with the "UserID" as the joined columns. The data needed to retrieve is in the TBLLaptop column "EncryptionStatus" In simple terms if the record exist the Else statement I am trying to construct is as follows..

Else
Forms!FRMNewLaptopBookingV2!EncryptionStatus = Table!Laptop!EncryptionStatus
Me.UserID.SetFocus
End If
End Sub

I get no errors, it just doesn't fill the vacant fields in.

Qustion one: Is my syntax referring to the table correct?
Question two: Should I be referring to the query and if so what is the syntax? Query!QRYNewBookingV2!EncryptionStatus ?

As always, thanks for your help!
Robert

 

[dhookom]

I am very confused. I can't tell if the table name is [Laptop] or [TBLLaptop].

You can't use an expression in code like "Table!Laptop!EncryptionStatus". Even if it did work, this expression would not be limited to a single record. You can use DLookup() in code to pull a single value from a table or query.

Isn't this field already in the form's record source? Why would you need to look it up in table.

Generally it is considered bad practice to store a value that can be looked up in another table. Perhaps you should provide the significant table and field names as well as the field data types?


Duane
Hook'D on Access
MS Access MVP

 

[RobertIngles]

Sorry about the confusion in my question - I am trying to go a differenct route but still running into difficulties

I have created a new form based on a query that will display information only from TBLLaptop and TBLUser (related field is [UserID]. I then plan to create a subform for booking selection.

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 no it opens a blank screen.

How do I set this form up so the user enters a valid [LaptopID] matching the same field in TBLLaptop 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?

Apologies that this question is being asked as part of two threads - not done intentionally.

 

[dhookom]

Is this the same question as your other recent thread in this forum? If so, please reply with a note to ignore this thread and point to the other one where MajP is assisting.

The control the users enter a value into should not be bound to a field as suggested in the other thread.

Duane
Hook'D on Access
MS Access MVP