Filter an open form using a query

[vipersmind]

Hi
I have a Form [frmDetails], which is bound to a table [tbleHeader]
and contains a sub form [frmSubSamples] which is bound to a table [tblSamples] both forms are linked by [FaceID] primary key.

On frmDetails the user enters the header information for each FaceID and on frmSubSamples enters all of the samples for the same FaceID.

The Faceid's are incremented by one by the user every time a new one is entered. (I would like to have this increment automatically but there are way to many variables - mostly human ones)

The user also needs to find the maxFaceID to ascertain which number to use next. I can't just goto lastrecord as there are many series of FaceID's on the same table.

i.e.
BD3430EOD01
BD3430EOD02
BD3430EOD03
BD3430EOD04
PE1710SXC01
PE1710SXC02
PE1710SXC03

I would like to use a query trigged by a cmdbutton to filter the already open form for the maxFaceID and have it display.
Currently the query runs and the user has to search the form manually.

Thanks in advance

 

[dcx693]

How exactly is that FaceID number constructed? If there are too many human factors involved, then the computer won't be able to find the last one entered unless you've got some other bit of info like a date/time stamp.

I posted a response in a thread about finding the max value in a field here but it won't work for you because you've got other stuff in the FaceID field.

You could run a query that groups on certain parts of the field. The first 8 characters seem to be a major grouping.

Tell us some more about the data and we can help you out.

 

[vipersmind]

Sorry, It's always hard to explain something you do and understand as if by second nature to some one outside.

The mining industry by its nature is never consistent. Engineers are in control, or out of control depends which way you think.

Hope this clarifies

Controls:

FaceNum = 01
FaceID = BD3430EOD01
Decline = BD (combobox)
Drive = 3430EOD

In the Afterupdate event of the Drive

Me!FaceID = Me![Decline] + Me![Drive] + Me![FaceNumber]

I have a query which Asks [which Decline?] then [which drive?]
it then finds the maxFaceID using the last two digits [FacNum]

I don't have any problems getting this value.
I want to then filter frmDetails using the value from the query.