Access combo box

[NathanArreola]

My combo box on my navigation page won't pull the records for the items I select from the drop down. It instead will go through the records sequentially going from one item to the next rather than allowing me to skip to records for item number 926

[#2]

what code do you have on the combo? can you post?

[#3]

@NathanArreola 

Keep in mind that the only thing we can know about this form and combo box is the information you share in your post. To that end, we need enough detail and context to work with. As Arnel asked, the code that runs on the After Update event of the combo box is the minimum amount of information needed.

It's not clear, though, what it means to say that it goes through records sequentially, rather than skipping. Please explain that in a way which allows us to visualize the actual process.

[#4]

@George_Hepworth 

Hey George

Sorry I have very little experience with access so I wasn't sure how much to include.

For a little bit more background on my situation, the other day my entire file crashed and deleted all of my macros and interactive procedures while maintaining the data stored in that file. I have managed to reconstruct everything so that way the entire database is usable except this one combo box. The purpose of this combo box is to search through thousands of equipment records and pull those specific to the unique equipment ID typed in or selected from the drop down. The error that I mentioned is that when I put a unique ID into the drop down it will not pull those records rather the records for the next sequentially ordered piece of equipment documents. For example I open up this equipment window by default it displays the record for equipment number 1 I then type in equipment number 926 or choose it from the drop down and hit enter. The page refreshes but instead of pulling records for 926 it pulls records for number 2 if I repeat the process it goes to number three and so on. I've attached pictures to show the properties pages and the coding view as well.

The funny thing too is I have an outdated back up that works completely fine but is missing hundreds or thousands of records so I can't simply switch over. When I go into design view for that files combo box the properties sheet for each are identical so I cannot for the life of me figure this issue out.

Let me know if you need any other information

[#5]

arnel see update below

[#6 · Accepted solution]

@NathanArreola 

From the final picture in your screen shot, it would appear that a sub was possibly lost and not replaced. You need to find the sub called "Combo32_AfterUpDate()" and restore it. If it was lost, go to the most recent backup before the crash.

But that brings up another factor. I am assuming, of course, you have a backup protocol in place and that you do have available backups. If not, today is the best time start doing that.

Also, it matters a lot if your relational database application is properly split into an accdb containing only the interface objects (forms, reports, VBA and queries) and an accdb containing only the data, in tables.

If that's not already in place, do that as soon as you set up back ups. Maybe before doing that even.

Users  get only a copy of the interface (usually referred to as the Front End or FE) on their own computer. That way, if one of them biffs their copy, you just replace it from the Master copy which you keep safe. All of the FEs connect to one accdb (usually referred to as the Back End or BE) in a shared folder on your network to which all users have proper read/write/delete rights. 

As noted, though, there should be code behind the After Update event that selects the proper records as you wish. 

[#7]

@NathanArreola 

A while back I wrote a blog article on asking questions on forums. It's a light-hearted attempt to point out the importance of including too much information rather than too light.

[#8]

@George_Hepworth 

I see that I missed the last paragraph regarding the property sheets being identical, but  the code in the After Update event may not be. Let's verify that. And please copy/paste the code, not a screenshot.

[#9]

@George_Hepworth 

Hey George we do have a back up schedule but we chose to back up the shared drive and the knowledge of how the application works was lost to us, when all the employees that designed the database left the company. Rest assured I will 100% be performing routine back ups, especially after this occurred. I will bring up to my lead your suggestions on the split of the file.

In the code design view I was able to select combo 32 then the after update from the drop down here is the code I found.

Private Sub Combo32_AfterUpdate()

End Sub

You were right though because this is what we have for the code of the out dated backup.

Private Sub Combo32_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[EquipID] = " & Str(Nz(Me![Combo32], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

[#10]

@George_Hepworth 

Hey George also how do I go about building out the code to match? I tried copying and pasting the code over in the design view but nothing changed. I want to make sure I'm doing this correctly to not cause any confusion.

[#11]

It would involve two or three steps.

Copy the code.
Make sure the property sheet indicates that the control does have an [Event Procedure] and that the code is in it.

Also make sure the two controls (i.e. the combo boxes) are identically named.

[#12]

@George_Hepworth 

It worked!! thank you so much George!