Open Table To Specific Record (1 Viewer)

[Reese]

[Solved] Open Table To Specific Record

Is it possible to use VBA or macros to open a table to a specific record?

I know how to open a table in database view using a command button. I'd like to have it open to or automatically scroll to and select a specific record, based on the content of the form. This way I don't have to spend time scrolling through the whole table to try and find the desired record.

Thanks!

 

[Minty]

I would make a simple form to display your records, then add a command button to filter the records to the desired record. Probably using an Unbound filed to enter the record number or whatever filed you are looking for.

Editing records directly in tables is fraught with pitfalls.

 

[Reese]

Editing records directly in tables is fraught with pitfalls.

Don't worry, I am well aware! This is for a very specific upkeep purpose. I am using a form to run a find duplicates query in my clients table. There is a one-to-many relationship between the clients and events table. If the same client was accidentally entered twice I will need to change the Client_ID field (the key that links the two tables) in the event table for at least one record.

Now that I think about it, would I be able to do this within a form? If the form is bound to the appropriate record in the events table and there is a text box for the Client_ID field, I should be able to change it, correct? So long as I make sure that I know what to change it to.

Do you think that would be a better way to achieve what I am looking for?

 

[Minty]

I wondering if you could prevent the issue occurring at the point of data entry.
Could you restrict the Client_ID to only show if they aren't already listed on the event?

 

[Reese]

I wondering if you could prevent the issue occurring at the point of data entry.

I have already done my best to do this. Before creating a new event, we ask if the client has booked with us in the past. We search for them and then open the form to book a new event with them instead of first creating a new client entry.

This is simply to correct human error. In case the client didn't remember, we forgot to ask or search before entering the event, etc. We've been using this database for the past year and have almost 300 clients in the database and about 50 have booked multiple programs. Yesterday I checked for duplicate clients and found 4. So the process has worked, just with a few mistakes.

Could you restrict the Client_ID to only show if they aren't already listed on the event?

In all of the forms that I have already made, the Client_ID is hidden and the users can't change it. This is the first (and I think probably only) circumstance that there will be any kind of direct contact with that field.

 

[Minty]

We've been using this database for the past year and have almost 300 clients in the database and about 50 have booked multiple programs. Yesterday I checked for duplicate clients and found 4. So the process has worked, just with a few mistakes.

TBH if it is that few over the year I would save a query to show the duplicates and directly edit those in the query results datasheet. (Assuming its a editable query). Alternatively when in the table simply right click and filter by the client_id, that will give you all the matching records very quickly.

 

[Reese]

Thanks for the suggestions. I already have the query saved; I don't think it's editable the way that you suggested but I'll take a look.