How do i attach a two date fields and a combo box to report the outcome?

[fairy56]

I have a query which runs from three lots of information i enter. This information is as follows
Supplier Name
Start Date
Finish Date

i enter these currently by the automatic pop up boxes when the query is run. However i want to create a form containing all three of these fields and then a 'go' button.

I have created the form containing these by using a combo box for the supplier name and text boxes for the dates but i cant get the button to then read from these and run the query, it still asks me for the fields again when i press the button....

any help? Preferably not in VB codeing if possible!

Thanks in advance!

[hedges98]

In the query criteria for each you need to reference the relevant combo box/text box.
It'll be something like

[Forms]![YourFormName]![YourTextboxName]

I think!

[fairy56]

Yeah i've done that.
In the suppliers criteria it says
[Forms]![Dateform].[cmbsearch]
Start date is
>[Forms]![Dateform]![txtStart]
Finish date is
<[Forms]![Dateform]![txtFinish]

All match exactly with the names

[JeremyI]

What sort of 'automatic pop up boxes' are you seeing? Are these the ones Access generates when it can't find a specified field? If so, I'm not sure it is able to save the records to a table at all, so that could be part of what is going on.

If the button was created with a wizard, could post the code for OnClick here? It might be possible to give better answers after looking at that.

[fairy56]

The pop up boxes are asking for the Parameter value. i've tried taking a print screen and copy it here but it wont let me!

It asks for the parameter value for each of my criterias.

The codeing for my button is below:

Private Sub CommandDates_Cl ick()
On Error GoTo Err_CommandDate s_Click

Dim stDocName As String

stDocName = "Non conformances Supplier dates"
DoCmd.OpenRepor t stDocName, acPreview

Exit_CommandDat es_Click:
Exit Sub

Err_CommandDate s_Click:
MsgBox Err.Description
Resume Exit_CommandDat es_Click

End Sub

This was made using the wizard.

Are criterias are set to match the names of the text boxs and combo box. have i done something wrong at this stage?

[fairy56]

Ok. now something strange is happening and its not picking up any of the criteria its just giving me a blank report.

any help please?

[fairy56]

ok just to let you know where im up to.

the query now has the following criterias which are working by entering them in the parameter boxes when they pop up

Supplier name
[Forms]![Dateform].[cmbsearchdate]

Start date from
>[Forms]![Dateform]![Datefrom].[txtStart]

Finish date to
<[Forms]![Dateform]![Datefrom].[txtFinish]

if i take out the [Datefrom] in the criteria for the dates i dont get any parameter boxes and just a blank report.

my button is created from a wizard to run the report which was created based on the query. coding is below


Private Sub CommandPreview_ Click()
On Error GoTo Err_CommandPrev iew_Click

Dim stDocName As String

stDocName = "Non conformances Supplier dates"
DoCmd.OpenRepor t stDocName, acPreview

Exit_CommandPre view_Click:
Exit Sub

Err_CommandPrev iew_Click:
MsgBox Err.Description
Resume Exit_CommandPre view_Click

End Sub

But when i press this i get parameter boxes for both date fields but nothing for the supplier. However even if i enter dates i still get a blank report!

Im confused :( any help please?

Thanks!

[JeremyI]

Sorry, fairy56, I didn't realise before that this was a form to launch a report; that must be why the two date boxes are unbound, right?

In my experience, the Enter Parameter dialog box--when it's asking you for information that should already be available somewhere--generally means Something Has Gone Wrong, so you probably do want to get rid of it. I don't know if I will be able to help all that much, being fairly new myself, but I can suggest a few next steps to try that might narrow down what is happening.

First, from what I've been reading, for active form controls it is best to use exclamation points for the entire control's name, as in [Forms]![Dateform]![cmbsearchdate] . But I doubt that is the issue here, as Access tends to compensate.

In the query criteria, you could try >= and <= rather than > and < , in order to include the start and end dates themselves.

If you enter the data into your form and then open the query object (before clicking the command button), you can check on whether it actually returns any records. You can also check whether there should be any records in it; set a break point (F9) when you have the following line selected in the code window:

DoCmd.OpenReport stDocName, acPreview

Then open the form, enter data, and press the button, and when you reach the break point, open the Immediate pane (Ctrl-G) and check that Access is picking up the expected values, like so:

?[Forms]![Dateform]![Datefrom].[txtStart]

or

?[Forms]![Dateform]![txtStart]

... and so on (note the question marks). If it does not show any values, that would mean the query would not find any matches. If there are values there, something's wrong with the query criteria.

Let us know if none of this works and then hopefully someone with some experience can step in. ;-) Good luck!

[rwalle]

Fairy56 :

I have done some reports based on querys that are based on ounbound fields from a form, the main issue I have had is the format, does your initial and final dates from your form are same format as the field in the query ?, actually I first fill the criteria box on the query design with dates as i would use in the form, test the query to see if it show some info and then if it works I fill the query criteria box with the [Form]![FormName]![SearchedFieldna me] and thats it