Solved Combo Box Search (1 Viewer)

[Emma35]

Hi All,
I'm putting together a database to keep records of machine servicing. The attached file is just a little test i threw together with no formatting or anything just so you can see what i'm trying to do. The main form holds information on the machine while the sub-form records the servicing details each time. What i'd like to know is would it be possible to put a combo box on the sub-form which lists each date on which it was serviced and when i click on that date, the sub-form jumps to that record ?
Bear in mind that after a while each machine could have a lot of servicing records so i'd just like to have a way to go to the one i need quickly, without using the navigation buttons at the bottom.

Any help would be great, thanks
Em

 

[Ranman256]

This filters the date field , if date has no time.

Sub combo_afterupdate()

If isNull(cboBox)
  Me.filterOn=false
Else
  Me.filter=[field]=#" & me.combo & "#"
Me.filterOn=true
End if

Now this won't filter the report subReport.
The subRpt query will need to use the combo box too.

 

[theDBguy]

Hi Em. Have you tried adding the combobox using the Wizard and selecting the third option?

 

[Emma35]

Thanks for the suggestions guys.

Ranman: I tried the code but it's giving me an error and highlighting the hashtag symbol in the third last line

DBGuy: I tried to add a combo box to the subreport but the wizard won't activate.. It only works when i put it on the main form. I created a query and added the MachineID and LastServiced fields from the tbl_ServiceRecords and used that as the control source for the combo box. It gives ma a list of service dates for that machine but how do i get the subreport to move to that record ? Edit: Actually now that i read this again that's not going to work anyway is it ?

 

[Gasman]

Hi Em. Have you tried adding the combobox using the Wizard and selecting the third option?

I think that should be

Me.filter="[field]=#" & me.combo & "#"

Do replace [field] with the actual name of your DB field.

 

[theDBguy]

Thanks for the suggestions guys.

Ranman: I tried the code but it's giving me an error and highlighting the hashtag symbol in the third last line

DBGuy: I tried to add a combo box to the subreport but the wizard won't activate.. It only works when i put it on the main form. I created a query and added the MachineID and LastServiced fields from the tbl_ServiceRecords and used that as the control source for the combo box. It gives ma a list of service dates for that machine but how do i get the subreport to move to that record ? Edit: Actually now that i read this again that's not going to work anyway is it ?

Hi. To make the Wizard show up, try opening only the subform in Design View. Also, make sure to bind the combo to the ServiceID field and just filter it by MachineID.

 

[Micron]

I might reconsider the whole approach. Not likely that you know the exact service dates for a particular machine, and you could have that field in a continuous or datasheet subform and just scroll to a date you have in mind. If that is too difficult, consider that your date rows in any combo for looking up a date would have to be dynamic and depend on which machine is in the main form record. Each time you navigate, the combo row list needs to be updated as it applies to that machine. Not impossible, but there are simpler approaches IMO.

Another would be to use two unbound date fields on the main form to provide a date range, which is probably the information you only have to work with any way (a notion of a date rather than an exact date). When both dates are entered, your subform displays records falling between those dates. If you know the exact date, simply enter it 2x.

 

[Emma35]

I managed to get the wizard to work but still can't get the combo box to change the record. I'm attaching what i have and i'm sure i've done something stupid !.
Micron thanks......i'll give your suggestion a go if i can't get this to work. I'm trying to avoid using the datasheet view as it wouldn't be practical in this instance

 

[Micron]

You have no event for the combo update. You have some code for a button named cboDates but your combo is named combo10?
The main reason this can happen is because you have at the top
Option Compare Database
but not
Option Explicit
Go to Options in the vb editor and turn on 'require variable declaration', which will add that to any new (but not existing) modules.

Then connect the procedure to the proper control name by choosing the event from the property sheet.

 

[Micron]

Also, your combo row source first column value is the ID field from the table (autonumber id). You will need to set the bound column to be the date field (column 2) otherwise you are passing the number autonumber (e.g. 3) to the date filter, not the date you see in the combo.
You could also just not bring back the id as I don't think you're going to use it anyway. In that case, you'd leave the bound column as 1.

 

[Emma35]

The code you saw with the combo box cboDates was when i was giving ranman's code a try....i just forgot to delete it. I've turned on 'require variable declaration' . So what do i need to put in the 'After Update' event to make it work ?

 

[bob fitz]

Perhaps this is what you want:

 

[Emma35]

Yes bob that's exactly what i wanted it to do......i see the changes you made to the row source and After Update event. If it's not too much trouble can you tell me the reasoning behind it ?....i just want to try understand what's happening. Thanks a lot for your help

 

[bob fitz]

Yes bob that's exactly what i wanted it to do......i see the changes you made to the row source and After Update event. If it's not too much trouble can you tell me the reasoning behind it ?....i just want to try understand what's happening. Thanks a lot for your help

I'm glad it was of use to you. I'll do my best with some explanation but please don't hesitate to ask if you have any questions
The Row Source of the combo box includes and is bound to the PK of the Service records as this is the best to search for. The "LastServiced" field is included to show the actual dates available to be picked. The "MachineID" field is included so that criteria can be applied to the records shown in the combo box.
The code in the AfterUpdate event just finds the required record.
I also put one line of code in the subform's OnCurrent event to requery the combo box. This just ensures that the records returned are correct as you move to different records. With hindsight, that requery command should also be in the combo's GotFocus event so that newly added records would be shown.
Hope this is of some help

 

[Emma35]

Thanks a lot bob it was very helpful. Also, thanks to everyone who took the time to make a suggestion...it's very much appreciated.

Emma xx