Design an Access Form with Dynamic SQL as Recordsource

Question

I am pretty new to Access VBA programming. Here is one problem I have when creating a form in Access. I need to link the form's recordsource to a query object which I have already defined. Let's say I have field1, field2, field3, etc. in my query. The end product I would like to present is

on top of the form, there are several text boxes for user to input filtering criteria of field1, field2, field3 ... and
on a click of a button, a datasheet displays at the bottom of the form with the filtering criteria applied
besides the existing fields in my query, I need to include at the end of the datasheet a customized column based on certain calculation result from field1 and field2. Some text could be displayed, for example "Late", "Early" or "On Time"

I have come up with two design approaches, but due to the lack of experience in VBA programming I am not able to accomplish either one of them right now...

Design 1:

I have a main form with the criteria input text boxes. This main form is not linked to any data source. Then I have a subform at bottom whose data source is dynamically set by reading the text boxes' values in the main form.

Design 2:

I have a single form, but I shift all the criteria boxes to the Form Header section. This form has a record source from my query object. The detail section is used for displaying the data sheet with the form's filter property set to user criteria.

I am not able to do design 1 because I don't know how to set the data source for my sub form dynamically. I am also not able to do design 2 because my form's header section does not appear even I have checked in the design view the form header/footer is enabled.

What can I do to realize my form design? Any alternative design suggestions?

I am using Access 2003 for this development, but I hope my code can be upgradable to Access 2010 in future.

Thanks in advance for your advice.

for design 1, I would also like to know if the subform should be inserted originally as an unbound object. and later do I need to set the data source of each field in this sub form? — got2nosth
Have you considered referring to the criteria fields as subform link child fields? Link Master Fields: txtField1, txtField2 -- Link Child Fields: Field1, Field2. The main form does not need to be bound to a recordsource, you can refer to the controls themselves. It may not suit, but if it does, it is by far the best way of filtering a subform. — Fionnuala
@Remou I haven't thought of that way. but when I have the source object of my subform set to a query object and tried to link child fields, I got the error "can't build a link between unbound forms". by setting the source object this way, can I still add extra columns to the sub form? and is the linking relationship one-to-one, like Field1=txtField1, Field2=txtField2... how may I specify a condition like between two field values? thanks — got2nosth
As I said, it may not suit. You cannot have Like. The subform must be bound to the full data set. It looks like you need to set the subform recordsource dynamically. Do not forget that the name of the subform control (not the form contained) is used when referring to a subform from the main form. This may help wiki.lessthandot.com/index.php/… — Fionnuala
You can also set the form content of a subform control dynamically, so if you have not included a form in the subform control, do so, before you try to refer to it. — Fionnuala

mnieto mnieto · Accepted Answer · 2014-01-08T08:23:41

With both designs you should build the datasource dynamically, something like this:

sql = "SELECT * FROM MyTableOrQuery WHERE 1=1"
If Not IsNull(textBox1) Then
  sql = sql & " And Field1 = '" & textBox1 & "'"
End If
If Not IsNull(textBox2) Then
  sql = sql & " And Field2 = '" & textBox2 & "'"
End If

And, finally, assign the sql to the form datasource.

Design1

The below code goes in the main form, just next to the above:

SubFormControlName.Form.RecordSource = sql

Design2

To show the controls in the header section you need to set the property DefaultView to running forms (I don't know the extact translation, I work with Access in spanish) In this case, the datasource is assigned to the main form

Me.recourdsource = sql

For your third point: You can build calculated field using

Iif(fieldA<5,"Early",Iif(fieldA>10,"Late","OnTime"))

or you can use conditional formatting. I think this option is most clear

Design an Access Form with Dynamic SQL as Recordsource

1 Answers