Question Correct approach to using a combobox to select records to edit on a form

[PlasticMonster]

Hi All,

My first post on this forum. As most people start with..."ive been teaching myself Access and Ive got a few problems I cant solve"...

I am creating a data base to handle access requests to a building. All has gone well so far and ive built tables, reports, forms and used queries. However now im trying to get abit more clever and ive hit a bump of understanding/apprach.

Whilst a ninja in Excel, im still working out which is the pointy end in Access.

The database holds all details of access requests inc: Company attending, Individual attending, Access Levels and Period of attendance. This is all done with forms for the users and functions beautifully (ish).

I can run reports on this data, based on queries (there is much more included than above but you get the idea) and generate all the reports I need.

What I wanted to do was add, following attendance to the site, the card details of the AC card they were given for the visit.

My intention was to have a form with three variables: a combobox that would let you select the individuals company and two text boxes to select a date range in. Leaving just say three or four people from that company on that day rather than everyone who had ever atteneded to sort through and add the card details.

I though this would be best acheived via making a query that I take the form detail from.

The combo box comes from another query that gave me individual company names from the main table.

I thought a date query (as in placing a more than <> or less than criteria vs textbox value on form in the query build section) could be added but I hadnt got that far.

It seems what I have done works backwards (oops), I can adjust the query from the form but get every record in the table on the form to click through to add card details, which will be abit rubbish when i have 1000's of requests building up in the history.

I think I may have the wrong approach, a little steer would be greatly appreciated.

Please....

 

[NeutronFlux]

I'm not completely sure if I'm getting it right, but are you making a search form for AC cards, something that would look like this?

[cboCompany] [txtDate1] [txtDate2]
[ Filtered multi-item results ]
[ Filtered multi-item results ]
[ Filtered multi-item results ]

If so, one approach is to use a subForm and Form Filters in VBA.

Something along the lines of

Dim strFilter as String
Dim lngLen as Long

strFilter = ""
If Not IsNull(Me.cboCompany) Then
 strFilter = strFilter & "([Company field] = " & Me.cboCompany & ") AND "
If Not IsNull(Me.txtDate1) Then
 strFilter = strFilter & "([Date] >= " & Me.txtDate1 & ") AND "
If Not IsNull(Me.txtDate2) Then
 strFilter = strFilter & "([Date] <= " & Me.txtDate2 & ") AND "
lngLen = Len(strFilter) - 5
If lngLen > 0 Then
 strFilter = Left$(strFilter, lngLen)

Me.subForm.Form.Filter = strFilter
Me.subForm.Form.FilterOn = True

Allen Browne's website has some more detailed examples.

 

[PlasticMonster]

Thanks Neutron Flux

I am trying the code I think I understand what references what, however im getting and error in the last two lines..

I'm getting
compile error: Method or data member not found

Code...
Me.SubForm.Form.Filter = strFilter
Me.SubForm.Form.FilterOn = True

Ive tried using this change to refer to my cells...
Me.subform.sdc card update form.Filter = strFilter
Me.[Forms]![sdc card update form].FilterOn = True

Im getting something wrong. I cant quite understand the section on this on Allen Browne's website. Im still researching but thanks for the pointer.

next stop...
google = "Subforms Access 2010"

 

[NeutronFlux]

Thanks Neutron Flux

I am trying the code I think I understand what references what, however im getting and error in the last two lines..

I'm getting
compile error: Method or data member not found

Code...
Me.SubForm.Form.Filter = strFilter
Me.SubForm.Form.FilterOn = True

Ive tried using this change to refer to my cells...
Me.subform.sdc card update form.Filter = strFilter
Me.[Forms]![sdc card update form].FilterOn = True

Im getting something wrong. I cant quite understand the section on this on Allen Browne's website. Im still researching but thanks for the pointer.

next stop...
google = "Subforms Access 2010"

Just to make sure, but did you replace 'subForm' with the name of the control on your main form? It will generate such an error if you didn't.

To give an example, let's say you have two forms called frmMain and frmAC. You create a subForm in frmMain linked to frmAC. Now you click on the subForm (frmAC) and go to properties. Change the name to something like subAC. Now the code would be:

Me.subAC.Form.Filter = strFilter
Me.subAC.Form.FilterOn = True