Solved Using Combo box to go to record

[tmyers]

I have a combo box on my form that populates with options to move to different record within a quote.
The problem I am having is when the form is refreshed/requeried, the form goes back to its original state/value. Regardless of what the combo is on. How can I prevent that?

 

[theDBguy]

Hi. How are you refreshing the form? If using code, just call the same code in the combo after the refresh.

 

[tmyers]

Hi. How are you refreshing the form? If using code, just call the same code in the combo after the refresh.

I just did a button and used the macro builders requery.
I did initially try to build the combos code into the refresh, but it didn't work as expected. It appears the combo doesn't hold its value and instead goes to the first in its list.
I will keep messing with it.

 

[tmyers]

Was side tracked on a report issue.
Here is the combo-boxes macro:

This works, but I also believe it is part of the problem. When other refresh buttons are pressed (I say refresh but most are requery), the combo losses the value it is on and goes to the first in list, which then "flips" the form to a different value.

I think I can maybe fix this by actually writing out the code in VB rather then using the macro builder. I will give it a try.

 

[MajP]

One thing if you go to code, the trick in returning to a record is something like this

dim ID as long
id = Me.SomePrimaryKey 'name of your primary key
me.requery ' requery the form
me.recordset.findFirst "SomePrimaryKey = " & ID 'return to the record you were on

save the value
Do a requery
return to the value.

This usually is so quick you barely see movement unless the amount of records is very large.

 

[tmyers]

One thing if you go to code, the trick in returning to a record is something like this

dim ID as long
id = Me.SomePrimaryKey 'name of your primary key
me.requery ' requery the form
me.recordset.findFirst "SomePrimaryKey = " & ID 'return to the record you were on

save the value
Do a requery
return to the value.

This usually is so quick you barely see movement unless the amount of records is very large.

I have never seen that method done. I may give it a try.
I was doing:

Me.Filter = "[JobID] = '" & Me.RevisionNameCombobx & "'" & " AND [RevisionName] = '" & Me.RevisionNameCombobx.Column(1) & "'"
Me.FilterOn = True

However it isn't quite working. I dont think I am delimiting correctly. JobID is a number, and RevisionName is text.

 

[MajP]

to help yourself to debug. I always do
dim fltr as string
fltr = "[JobID] = '" & Me.RevisionNameCombobx & "'" & " AND [RevisionName] = '" & Me.RevisionNameCombobx.Column(1) & "'"
debug.print fltr 'now read it to make sure it looks correct
me.filter = fltr
Me.FilterOn = True

Try
fltr = "[JobID] = '" & Me.RevisionNameCombobx & "' AND [RevisionName] = '" & Me.RevisionNameCombobx.Column(1) & "'"

 

[tmyers]

to help yourself to debug. I always do
dim fltr as string
fltr = "[JobID] = '" & Me.RevisionNameCombobx & "'" & " AND [RevisionName] = '" & Me.RevisionNameCombobx.Column(1) & "'"
debug.print fltr 'now read it to make sure it looks correct
me.filter = fltr
Me.FilterOn = True

Try
fltr = "[JobID] = '" & Me.RevisionNameCombobx & "' AND [RevisionName] = '" & Me.RevisionNameCombobx.Column(1) & "'"

I am getting "The search key was not found in any record". I haven't seen that before

I used:

Dim fltr As String
    fltr = "[JobID] = '" & Me.RevisionNameCombobx & "' AND [RevisionName] = '" & Me.RevisionNameCombobx.Column(1) & "'"

    Debug.Print
    Me.Filter = fltr
    Me.FilterOn = True

Debug looks like its returning the correct values:
"[JobID] = '137' AND [RevisionName] = 'Base'"

 

[MajP]

Looks like jobID is numeric so remove the single quotes around the id.
fltr = "[JobID] = " & Me.RevisionNameCombobx & " AND [RevisionName] = '" & Me.RevisionNameCombobx.Column(1) & "'"

 

[tmyers]

Looks like jobID is numeric so remove the single quotes around the id.
fltr = "[JobID] = " & Me.RevisionNameCombobx & " AND [RevisionName] = '" & Me.RevisionNameCombobx.Column(1) & "'"

Ah yes you are correct. I work with text so much more than numbers anymore I mess that up constantly. It works now and doesn't have the issue of changing during a refresh/requery.

The macro builder is nice, but it causes interesting results sometimes. I have found it is better to just use VBA for the most part.

 

[MajP]

I usually put this function in all my databases. Then you never have to worry about the correct format of delimiters

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

You may find it real helpful.