Using a form to go to a specific record

[abl77]

Hi everyone,

I'm not very experienced at programming or MS Access, everything in my database is from the wizard that built it or through trial and error. I almost have it exactly how I want it after a couple months of playing around with it, but there's one thing i cant figure out. In my database when I open the Products form I want to be able to type in the part number in the box and go directly to the record for that part number instead of changing the part number of the record i'm on to a wrong part number. For example the first part number in my database is 0000001 and the form defaults to this when I open it. If I want to look at part number 0551234 I have to hold down the right arrow for a long time to get there since my database has over 3000 parts in it. Is there any way I can just type in the part number and go to that record? I have uploaded a smaller version of my database with only a few parts in it. Any help would be much appreciated. Thanks in advance.

Aaron

http://www.filesend.net/download.php?f=8f1d15705445ec8b0ca2e99938688e44

Also you can email me at aaronlittle77@gmail.com since the forum has that limit on uploads.

 

[RuralGuy]

Put a ComboBox on your form and select the third option from the wizard.

 

[abl77]

Thanks

Thanks,

This works great.

Aaron

 

[RuralGuy]

Glad I could help.

 

[Libre]

I'm not sure what the third option in the wizard is - and I'm glad it worked for you.
What I do, is just put the combo box on the form, unbound. The data source for the cbobox is the query of the key field - in your case the part number. Then you just program the "on click" event to take the value (or the text) of the cbobox and open the form that displays that record.
If you have tons of part numbers, you might want to use a list box instead of a combo box because you'll be able to see lots more records. You can also program the list box to go to the record you select. I do that one under the "double click" event.

 

[michaeljohannes]

Hello Aaron,

I have a suggestion that might help that is a bit more complicated but failsafe. The only issue with using the combobox wizard option 3 is that it creates an embedded macro instead of using a bit of VB code. This macro takes the action:

SearchForRecord (based on arguments you create)

The reason that this is a problem (it was for me) is because if you edit the Macro options there is a field at the bottom of the macro that says "record" with the following options:

First (default)
Last
Previous
Next

Well, what if you have multiple last or first names (for example)? It will only find the first, or the next, previous or last record. It won't necessarily find the exact record.



Solution: say you have 10,000 names of people with addresses. Each person should be given a unique ID (generally an AutoNumber) Let's call that

PersonID

Then you have a 'LastName' field and a 'FirstName' field (as well as any number of additional fields like address information, street number etc.)


So we have (for example....)

PersonID
LastName
FirstName


1) Create a combo box (without the wizard)

2) Go to the combo box properties and invoke the query builder by clicking on the "Row Source" button

3) Select the above fields (obviously they will be different in your database..) from your query or table and drag them into columns below.

4) Under sort (I recommend LastName) set it to sort ascending or descending. Close/Save the invoked query.

5) Now (back in design view) right click on the combo box and select code builder (to edit the Visual Basic code)

6) This is a bit tricky but you can do it! Select the combo box you just created and make sure you copy the following code in the combo box "AfterUpdate" sub. For example:

Private Sub YourComboBox_AfterUpdate()
Me.RecordsetClone.FindFirst "[PersonID] = " & Me![YourComboBox]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

(be sure to substitute "YourComboBox" for your actual combo box name)

Save and close the VB editor

7) Almost Done! Back to the design view of the form, select the properties of the combo box again.

Under the format tab, make sure the column count is set to 3 (this being the three fields you added to the invoked query - it could be 1 if you want or 5 depending on your field choices in this query)

Under the data tab, make sure the bound column is PersonID column. For example, if the third column in your invoked query is the PersonID, set the bound column to 3.

Ex:

column count is three (as below)

LastName FirstName PersonID
(ascending) (bound column)

8) That's it! It will work after you update it. Be sure to expand the size of the combo box so you can actually see all three columns when you click the dropdown arrow.



Sorry if this is long winded. I realize it's NOT a beginner bit of code. However, because this example selects a unique number (the AutoNumber, likely the primary key of the table) there will be NO issue with a macro only finding the first, last, previous or next available "LastName" or the "firstName" or any other field where there could be multiple identical values.

Also, this way, you can start typing a last name and it will populate this list (alphabetically if sorted ascending) so you can select the correct "Smith" or "Jones" or whatever common last name is out there.


I hope this helps! I know it works. If you have a problem, I would be more than happy to help you with your code

Best,
Michael