using form button, search table (via form), find/return info to form

[BedfordMA]

Hello

New to access, on the job training. I have existing form (Form1, orig. designer is gone), need to search table (Table1) for 'tracking id' which was previously entered (1 ID per person). If 'tracking id' is found, return all table information to the form to be edited/saved (part 2 after finding data - part 1). Have created a 'button' to search using an unbound text field (receiveID) where user will enter the known 'tracking id'. I have tried creating VB embeded macros, external macros, modules, some custom VB search/return code in MS VB screen. Nothing is quite working yet. VB code is not executing, no stub testing done yet. Looking for a correct overall direction.

1. What is the best way to search existing table: embededed/external macros, VB code, etc (runmacro, searchforrecord, etc)
2. Should the an 'embedded/external' macro 'call' another macro, module, VB code?


3. If I am writing VB 'search' code, where should it go/how to 'call'

I found a previous thread, that showed some VB search code (not debugged yet), but not how to execute it.

Thank you

BedfordMA

[jzwp11]

Welcome to the forum!

I need a little more information. Is the trackingID field the primary key of table1, i.e. is there only 1 record to be returned or more than 1?

What is the datatype of the trackingID field?

What form view (single, datasheet, continuous)?

Why not use a combo box from which the user can select the appropriate trackingID. In having them type it, you will have to handle what to do in the event of a typographical error.

[BedfordMA]

I need a little more information. Is the trackingID field the primary key of table1, i.e. is there only 1 record to be returned or more than 1?

Tracking ID is not the primary key. There is a random number generating field called 'request tracking ID' = primary key and that # is now being stored in 'tracking id' record

There are 10 records in table that could be filled in and returned.

What is the datatype of the trackingID field?

alpha, it will always be a number.

What form view (single, datasheet, continuous)?

I think it is datasheet, I have a datasheet design view.

Why not use a combo box from which the user can select the appropriate trackingID. In having them type it, you will have to handle what to do in the event of a typographical error.

I can try to create to a combo box, right now I am using a unbound text which I haved titled 'receiveID' with a 'button'. I will play with that this afternoon. I am in EST.

I will be still trying to get a way to seach the 'table1' ane return all possible records (VB or macro)?

Thank you!!!!

[jzwp11]

I decided to make up a little example database that I hope illustrates what you want to do. I used an unbound main form that has a subform bound to the table (table1). I used a combo box that uses a query I created. That query is a union query that pulls all unique trackingID numbers from table1 and unions it with the word ALL.

There is some code in the After Update event of the combo box that filters the recordsource of the subform based on what is selected in the combo box.

Hopefully it will help you achieve what you want.

[BedfordMA]

Hello
Just wanted to let you know I was sidetracking on an emegency and will be working
on you suggestion this afternoon. Thank you for the form example. Talk to you soon.

Have a great weekend!!!

[BedfordMA]

Ok, I have been working with the demo db sent to me. I cannot make it work for me. If I try this (with comments, entered by, requestor name part of my table) with the '! run' stub test command:

SELECT [ROI Tracking].[Tracking ID] FROM [ROI Tracking]; UNION SELECT 'ALL' as [Comments, Entered By, Requestor Name, Tracking ID] FROM [ROI Tracking];

I get my tracking ID's that exist in my table. I can't 'click' on them. If I run the it from form view in my form, I get a 'blank' box inserted into my form. I don't see the exisiting tracking id's. My goal (1) when I started this was to look for a 'created' tracking id, if found, return all existing values in my table associated with that id and bring back to my requesting form. Then (2) I wanted to be able to edit fields in the form and save the new values. Just trying to get (1) working and then (2).
Still working on it. so far tried macros, VB coding, and combo box, nothing yet working.
Thanks

[jzwp11]

Without seeing your table structure it is hard to diagnose the problem. Can you post your database with any sensitive data removed or provide a list of table/field names and relationships?

[CindyIvey]

Hi, just read BedfordMa's problem and your responses. I have a different problem. I have an Access form that is being used by 8 staff member around our State of Washington. I want to add new cases to the table each month and have them complete them at there own speed. What I would like is a way for them to click on a button on the form and it takes them to next available record that has not been completed yet. Then the staff person would go to a dropdown called QCS Number and assign the case to themselves. Once they finish that case they would click on the button to find the next availalbe case and so on. I have read all some articles on FindRecord and FindNext but everything I try fails.

[jzwp11]

...button on the form and it takes them to next available record that has not been completed yet

How do you determine that a record is not yet complete?
What determines the order?

Based on the answers to the above questions, you should be able to use a query to find the incomplete records and then select one based on some ordering criteria. This selection would take place via cod behind the button.

Once the record is found, you can have Access automatically open the form to that particular record, there would be no need to have the user select the record via the QCS number.

[BedfordMA]

Hello jzwp11

Haven't forgotton you, working on emergency work. Will get back to you asap.

Thanks

BedfordMA