473,322 Members | 1,614 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

11
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!
Feb 24 '10 #1
8 2182
hedges98
109 100+
In the query criteria for each you need to reference the relevant combo box/text box.
It'll be something like
Expand|Select|Wrap|Line Numbers
  1. [Forms]![YourFormName]![YourTextboxName]
I think!
Feb 24 '10 #2
fairy56
11
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
Feb 25 '10 #3
JeremyI
49
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.
Feb 25 '10 #4
fairy56
11
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_Click()
On Error GoTo Err_CommandDates_Click

Dim stDocName As String

stDocName = "Non conformances Supplier dates"
DoCmd.OpenReport stDocName, acPreview

Exit_CommandDates_Click:
Exit Sub

Err_CommandDates_Click:
MsgBox Err.Description
Resume Exit_CommandDates_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?
Feb 25 '10 #5
fairy56
11
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?
Feb 25 '10 #6
fairy56
11
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_CommandPreview_Click

Dim stDocName As String

stDocName = "Non conformances Supplier dates"
DoCmd.OpenReport stDocName, acPreview

Exit_CommandPreview_Click:
Exit Sub

Err_CommandPreview_Click:
MsgBox Err.Description
Resume Exit_CommandPreview_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!
Feb 25 '10 #7
JeremyI
49
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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. ?[Forms]![Dateform]![Datefrom].[txtStart]
or

Expand|Select|Wrap|Line Numbers
  1. ?[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!
Mar 1 '10 #8
rwalle
47
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]![SearchedFieldname] and thats it
Mar 2 '10 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Gleep | last post by:
Hey Guys, I've got a table called Outcomes. With 3 columns and 15 rows 1st col 2nd col 3rdcol outcome date price There are 15 rows...
9
by: Gleep | last post by:
sorry i didn't explain it correctly before my table is like this example fields: ID name username outcome date1 date2 date3 (etc..) - date15 price1 price2 price3 (etc..) I know that...
3
by: google | last post by:
I have a database with four table. In one of the tables, I use about five lookup fields to get populate their dropdown list. I have read that lookup fields are really bad and may cause problems...
1
by: Jo K. | last post by:
I have two date bounded fields in a form. DateA DateB I have two unbound text boxs (one overlapping the other - transparent background setting) that will display a message as per below: ...
2
by: Olveres | last post by:
Hi, I have managed to work out how to add new lines into a calculated text box. However in this text box some of the outcome fields are empty however when previewing the report it includes the...
8
by: Ragbrai | last post by:
Howdy All, I have a query that is used for filtering results to be used in a combo box. The query needs to test fields from both a table and then unbound text boxes on the form that also contains...
3
by: gmazza via AccessMonster.com | last post by:
Hi there, I am trying to run a report using a parameter for where the user chooses a month from a combo box. Then on the report, I want it to compare the month to a date field and choose only...
3
by: Fred's | last post by:
Hi Folks, I have a report which the record source is a query name: Query3 and in my Query3 I have this parameter "between And ". Therefore, when I open my report, it will ask for a Start Date...
2
by: yogeshtiwarijbp | last post by:
Hi All, Iam new in asp.net 2003 and sql server. I have to create an application having following criteria. Proposed Steps 1. Create a table containing fields as Name of Report, View Name or...
3
by: Nepenthen | last post by:
Hello – I am still new to Access and I am currently working on debugging some code. I have a form with the following: Employee Name (combo with select all choice) Task (combo with select all...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.