Best way to search recordset and set up a form? (1 Viewer)

[brianjessup]

What's the best way to search a recordset in VBA to see if several criteria match in a record?

For example, after a few controls are set, I'd like to search for any records matching those settings. Once found, I'd like to be able to edit a few other fields in that record via some txtboxes on the form and then save the record to my table.

Once the record is found, is it possible to then use a field to set a control?

For example, one of my controls on the form is a combobox, the first column (hidden) of which has the table key for each item - ie a number unique to that item. This is what is stored in the recordset I'm searching. Once a record is found matching things like date, location, etc, I'd like the same combobox to use the ID# to display the item name - or in other words I'd like to set the combobox to the item based on the hidden key. Is that possible?

How to return data to a function?
And finally, a restatement or addendum to the idea expressed in the first section. Once I get these records, I'd like to take data from a field, display it in a txtbox, edit it as i like and then (after the usual user prompts) save it.

I have a few basic steps here:

Set rst = New ADODB.Recordset

rst.Open "RawResults", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable

With rst
.MoveFirst
.Find "Day = 1"

Do While Not .EOF
'these next two if statements just check the other settings to
'make sure they match what I'm looking for
If Me!txtSetting1 = rst.Fields("Setting1") Then
If Me!FrameFinals = rst.Fields("Finals") Then
'load data
'set other controls
End If
End If
loop

End With
rst.close
Set rst= nothing
End Sub

 

[Travis]

Lets simplify this a little bit (let me know if this is to simplified)

And since you already have a form in which you want to edit the data how about using the Filter By Form Option. This is a Menu Item and can be run using the Docmd.RunCommand function. It will allow you to do simple to more complex filtering. The best part is that the most complex part of the Filtering code is already done for you.

 

[brianjessup]

maybe a good start

That may be the way to go, I'm exploring it now.

Could you (or someone) provide some example code?

The first step I'd like to accomplish is to filter down to a group of records based on some controls, then step through each one by one.

 

[brianjessup]

filter by form

Yeah, filter by form doesn't seem to be the way to go, since I'm trying to grab up to 8 records at once and manipulate some controls based on those 8 automatically, without the user getting involved and closeing the search.

 

[brianjessup]

form problem

Hi,
Seems that my original code worked fine, it was just a type mismatch problem. Even though the controls were in integers and the rst fields were in integers, the controls read as strings for comparison in VBA. So to make it work, I had to Cint(control.column(0)) before comparison. Also a txtbox control I had to change the format of that to be an integer.

Thanks All!

 

[Travis]

Ok let me make sure I understand what you want (please bear with me)


1. You have a Form with Controls on it that you want to use to filter data.

2. You want to be able modify the data of the records that are filtered automatically.

3. You want this to be done without user interaction.


===========================================
Questions:

How/Who will be setting the Sort Criteria for the Records?

Who will set the needed fields to be updated?

Who will set the value of the fields to be updated?

===========================================
If the user will be setting the criteria of what records are to change and what values these records are to change to then you can still use the Filter-By Form function to allow the user to create a custom Data filter. Then for the data what you can do is use the Form's Header Section to have "Unbound" versions of your controls in the Detail section. You can then add a button that will update the corresponding "Bound" field to equal the new value. You can then use the Forms RecordSet in code to iterate through the filtered recordset.

 

[brianjessup]

It's a bit foggy I know. That's only because I'm having to work around what I believe to be an Access limitation: Access won't store time down to millisec and still handle operations on it properly. I find this funny because I've managed to get Excel to work with millisec, so maybe I just don't know how to set up Access and I'm wasting lots of time with the workaround.

In any case, when the user sets a few controls, I'd like to filter down to records matching those controls. Then some data is loaded from these records. The data is in integer format, but represents the timecode in millisec. So I convert it to time through a Microsoft provided routine. The user then sees the data as Hours:Minutes:Seconds:millisec. When they edit the data, before it is saved, I convert it back to an integer.

Thanks for the help, but I've solved the search problem. I still don't know how to set the form controls that aren't part of the search. When a user uses a combobox to input the name of the device associated with the times etc., a value (hidden in column 0)is stored for the device. When the record is recalled, I don't know how to stick the name in the combo box based on the device number yet.

Any thoughts?