Show report help please (1 Viewer)

DigitalS27

Registered User.
Local time
Today, 13:47
Joined
Sep 13, 2013
Messages
13
Hi guys,

I have created a small database for entering samples received and condition of the sample.

I then created a simple report and added a button on a form with a start date and end date field.

So on the form the user selects the customer from a combobox, the user can then enter a start date and end date or leave the date boxes blank. then when the user clicks show report it should show the report. However, I can't seem to get this right. when I select show report without selecting a customer from the combobox I get a messagebox " a customer needs to be entered" this is part of the code.

But then I select a customer, and then it asks me to enter the customer ID / enter parameter value for some reason. doesnt matter what customer id i enter, it gives a blank report and then access wants to debug.

Please, please assist.

Below is the code used:

Code:
Private Sub cmdPreview_Click()

   Dim stDocName As String
   Dim strWhere As String
   Dim Tmp As Date
   Dim dtStart As String
   Dim dtEnd As String

   stDocName = "SampleInformation"

   If Len(Trim(Me.cboCustomer)) > 0 Then
      strWhere = "[Customer_Number] = " & Me.cboCustomer & " AND "
   Else
      MsgBox " A customer is REQUIRED!!!"
      Exit Sub
   End If

   'check the dates
   If IsDate(Me.txtDateFrom) Then
      dtStart = Me.txtDateFrom
   End If

   If IsDate(Me.txtDateThru) Then
      dtEnd = Me.txtDateThru
   End If

   'create the filter string
   If IsDate(dtStart) And IsDate(dtEnd) Then
      If dtStart > dtEnd Then
         Tmp = dtStart
         dtStart = dtEnd
         dtEnd = Tmp
         Tmp = Empty
         Me.txtDateFrom = dtStart
         Me.txtDateThru = dtEnd
      End If
      strWhere = strWhere & "[Sample_Date] Between #" & dtStart & "# AND #" & dtEnd & "# AND "
   ElseIf IsDate(dtStart) Then
      ' greater than date Start
      strWhere = strWhere & "[Sample_Date] >= #" & dtStart & "# AND "
   ElseIf IsDate(dtEnd) Then
      'less than end date
      strWhere = strWhere & "[Sample_Date] <= #" & dtEnd & "# AND "
   End If

   If Len(strWhere) > 0 Then
      'remove the trailing " AND "
      strWhere = Left(strWhere, Len(strWhere) - 5)
   Else
      strWhere = ""
   End If

'      Debug.Print strWhere

   DoCmd.OpenReport stDocName, acPreview, , strWhere

End Sub



:banghead::banghead:
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 20:47
Joined
Feb 19, 2013
Messages
16,553
I've modified your post to put your code between the code tags so it is more readable. Please use code tags (the #) button going forward as it makes the code easier to read and you are more likely to get responses
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:47
Joined
Feb 19, 2013
Messages
16,553
you say customerID, but your code says

strWhere = "[Customer_Number] = " & Me.cboCustomer & " AND "

and if cboCustomer is a string and not an ID then you need to include quotation marks

strWhere = "[Customer_Number] = '" & Me.cboCustomer & "' AND "
 

DigitalS27

Registered User.
Local time
Today, 13:47
Joined
Sep 13, 2013
Messages
13
you say customerID, but your code says

strWhere = "[Customer_Number] = " & Me.cboCustomer & " AND "

and if cboCustomer is a string and not an ID then you need to include quotation marks

strWhere = "[Customer_Number] = '" & Me.cboCustomer & "' AND "

Sorry I meant customer number and not CustomerID.

I have tried what you suggested, but still get enter parameter value error.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:47
Joined
Feb 19, 2013
Messages
16,553
so the parameter message asks you 'to enter the customer_number'

implies you do not have a field called customer_number in your report recordset.

Alternatively perhaps you have set report sorting or grouping on a field that no longer exists
 

DigitalS27

Registered User.
Local time
Today, 13:47
Joined
Sep 13, 2013
Messages
13
I have added a field customer_number to my report. but i still get the same error. can you please explain how i can edit this to remove the customer_number from my code?
 

DigitalS27

Registered User.
Local time
Today, 13:47
Joined
Sep 13, 2013
Messages
13
AFter I added the customer_number to the report, now when clicking the button to view the report I get the following error:

Data type mismatch in criteria expression
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:47
Joined
Feb 19, 2013
Messages
16,553
as mentioned before - you are trying to compare text and numbers

what is the rowsource to your combo?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:47
Joined
Feb 19, 2002
Messages
42,976
Put a stop in the code on the OpenReport method. Display the contents of the where clause in the debug window and paste it here if you can't see what is wrong.
 

DigitalS27

Registered User.
Local time
Today, 13:47
Joined
Sep 13, 2013
Messages
13
as mentioned before - you are trying to compare text and numbers

what is the rowsource to your combo?

SELECT [dfs_Customers].[Customer_Number], [dfs_Customers].[Customer_Name] FROM dfs_Customers;
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:47
Joined
Feb 19, 2013
Messages
16,553
OK so other things to check

is the customer number field datatype text or number?
what is the bound column of your cboCustomer control?
provide the sql to your report recordset

it might be easier if you upload a copy of your app - remove anything confidential, compact and repair then zip. Go to the advanced editor on the forum for the upload facility
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:47
Joined
May 7, 2009
Messages
19,169
upload your db to better understand.
 

DigitalS27

Registered User.
Local time
Today, 13:47
Joined
Sep 13, 2013
Messages
13
Thank you!!

Will upload now.

There is no confidential information as this is a very small app that prototype for our lab to just enter sample information when samples are received. Currently they do it in excel :)
 

DigitalS27

Registered User.
Local time
Today, 13:47
Joined
Sep 13, 2013
Messages
13
Uploaded database
 

Attachments

  • LIMS dfs.accdb
    1.5 MB · Views: 91

CJ_London

Super Moderator
Staff member
Local time
Today, 20:47
Joined
Feb 19, 2013
Messages
16,553
you said the cbocustomer rowsource was
SELECT [dfs_Customers].[Customer_Number], [dfs_Customers].[Customer_Name] FROM dfs_Customers;

what you actually have is

SELECT [Query1].[Customer_Name], [Query1].[Customer_Number] FROM Query1 ORDER BY [Customer_Name] DESC;
it needs to be the way you had it originally, just set the first column width to 0 to hide it

you also said your vba code is

strWhere = "[Customer_Number] = " & Me.cboCustomer & " AND "

when what you actually have is
strWhere = "[Customer_Number] = '" & Me.cboCustomerSel & "' AND "

you need to remove the single quotes.

For the future - please provide what you actually have, not something you think we want to see.
 

DigitalS27

Registered User.
Local time
Today, 13:47
Joined
Sep 13, 2013
Messages
13
y
For the future - please provide what you actually have, not something you think we want to see.

CJ, I did not provide something you want to see. I changed somethings this morning to try and get it working. I added a new combobox which I name cboCustomerSel as the other combobox was still on the form and could not give it the same name.

Also with the row source I tried using a manual query which I created this morning to see if that will help.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:47
Joined
May 7, 2009
Messages
19,169
here is your report.
 

Attachments

  • LIMS dfs.zip
    60.8 KB · Views: 81

Users who are viewing this thread

Top Bottom