Okay, I am finally broken down..

[robercs]

This is muy first time on the Forums so I apologize if this is in the wrong place.

I am a Rookie VBA programmer and have bought a few books to help. I have a need to mine some data out of an .mdb file and I was wondering if it is possible to post the resulting data on the same Form that the user enters in the search criteria? Basically I have a 3-item combo box and two date fields (start & end). I have found sample databases that seem to do it, but does anyone know where I can find more info?

The unfortunate part is, due to budget reasons I am forced to learn this on my own so any help would be greatly appreciated.

[CreganTur]

Welcome to the forum! Always good to see a new member.

The easiest way I can think of to accomplish what you want is to use a subform to display the data, and have your criteria fields feed the query that is the subform's RecordSource.

First, you need to setup your query. In Query Design view, type the following into the Criteria row of your desired field:
[Forms]![FormName]![ObjectName]
Replace FormName with the name of your form, and ObjectName with the name of the object on your form that contains the relevent data. Then, click on Query->Parameters. Enter the same [Forms]![FormName]![ObjectName] data into the first field, then select the correct data type. Do this for all of the criteria fields, and then click Ok.

Second step, create a form using your Query as a RecordSource- this will be your subform.

Next, use the Subform/Subreport tool on your ToolBox- this will create a window on your main form where your subform will be displayed. In the Properties sheet, select the Source Object property, and select the name of the new form you created.

You'll need to requery your form whenever the criteria changes. If you have a Submit button, you can put the following code behind it:
Me.Requery
This will update the Subform to show the new data. If that doesn't work, try:
Me.SubFormName.Requery
Change SubFormName to the actual name of your Subform.

HTH:thumb

[OBP]

Nicely described Randy :thumb

[robercs]

Thanks for the reply! Much appreciated, but another question I have is:
Can I post the code that I have written for people to look at or is that an unacceptable practice in the Forum? I would like to get some feedback on what I have, but would understand if code posting is frowned upon...

[CreganTur]

We love code samples, as long as you wrap VBA tags around them- click the green VBA button- this will format your code according to VBIDE, making it readable.

Also, if you have really long lines of code that are going to stretch the screen ,be sure to use the linebreak operator (the underscore character '_') so you can break it across multiple lines. It's very hard to look at code when you have to scroll to TimbukTu:thumb

[robercs]

Before I begin let me say that I am an extreme novice when it comes to VBA programming. Due to some restrictions in budget I am forced to learn this on my own. Some folks have mentioned that they like to see code on the Forums so here is my example. It doesn’t work by the way J so any advice would be helpful. I have tried to provide a little detail below, so hopefully you can get the idea of what I am trying to do.


Form description:
It is a fairly straight forward form. In the form header I have a combo box that contains 3 values: Portable, Van & none. I have two date fields “from” and “to”. Next I have a command button called Generate List. This is the button I want to generate the On Click Event Procedure.

In the Form Detail I have text boxes linked (=) to fields on the main data Table. The text boxes contain the information I want displayed on the Form. I want the user to select a value from the Combo Box and then enter the “from” and “to” dates they desire. From there once they hit “Generate List” I would like the data that fit the criteria to be flooded onto the form.

Listed below is the VBA code that I have been hacking away at. It has changed several times and I’m a sure it is really messed up…..



Sub cmdGenerateReport_Click()
Dim StrWhere As String
Dim strDates As String
Dim StrDocName As String
'Dim blnTrim As Boolean

If Not IsNull(Me.cboTestType) Then
StrWhere = StrWhere & "[Test Type]=" & Me.cboTestType & " And "
'blnTrim = True
End If


If IsNull(Me.txtStartDAte) And Me.txtStartDAte = "" Then
If Not IsNull(Me.txtEndDate) And Me.txtEndDate <> "" Then
StrWhere = StrWhere & "[finish (date)] <=" & Me.txtStartDAte & "#"
'blnTrim = False
End If

Else
If (Not IsNull(Me.txtStartDAte) And Me.txtStartDAte <> "") And (Not IsNull(Me.txtStartDAte) Or Me.txtStartDAte <> "") Then
StrWhere = StrWhere & "[Finish (date)] Between #" & Me.txtEndDate & "# AND #" & Me.txtStartDAte & "#"
'blnTrim = False
End If
End If
'If blnTrim Then
'StrWhere = Left(StrWhere, Len(StrWhere) - 5)
'End If
StrDocName = "Report2"
DoCmd.OpenReport StrDocName, acViewPreview, , StrWhere

End Sub
Private Sub Reset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
'Case acCheckBox
'ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.FilterOn = False

End Sub

[robercs]

The above code is the WRONG one! It actually works...sort of!

The code I meant to publish is...



Sub Go_Click()
Dim StrWhere As String
Dim lngLen As Long
Dim Portable As String
Dim Van As String
Dim none As String
Const conJetDate = "\#mm\/dd\/yyyy\#"
If Me.cboTestType = "Portable" Then
StrWhere = StrWhere & "([Test Type] = " & Me.Test_Type & ") AND "

ElseIf Me.cboTestType = "Van" Then
StrWhere = StrWhere & "([Test Type] = " & Me.Test_Type & ") AND "

ElseIf Me.cboTestType = "none" Then
StrWhere = StrWhere & "([Test Type] = " & Me.Test_Type & ") AND "
End If
If Not IsNull(Me.txtStartDAte) Then
StrWhere = StrWhere & "([End Date] >= " & Format(Me.txtStartDAte, conJetDate) & ") AND "
End If
If Not IsNull(Me.txtEndDate) Then
StrWhere = StrWhere & "([End Date] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If
lngLen = Len(StrWhere) - 5
If lngLen <= 0 Then
MsgBox "No Criteria", vbInformation, "Nothing to do."
Else
StrWhere = Left$(StrWhere, lngLen)

Me.Filter = StrWhere
Me.FilterOn = True

End If
End Sub

[CreganTur]

The issue you're having is a common problem for most new programmers. The Filter property, along with a number of other properties, requires a string expression to work with- you have that, but some othe properties explain that the string must be "like a SQL Where clause, without the WHERE." This means that you have to use data qualifiers in your expression.

Data qualifiers are symbols that SQL, or SQL similiar functions, like the Filter, read to determine the data type of a value it is being asked to work with. In your case, you need to wrap all of your concatenated textbox values with the correct symbol. For Strings, you use single quotes ('), for dates you use the pound sign (#); there is no data qualifier for numbers... so when you don't use them, SQL gets confused be cause it's expecting to work with a number and you're giving it a string.

Here's an example that rewrites a couple of your code lines:

If Me.cboTestType = "Portable" Then
StrWhere = StrWhere & "([Test Type] = '" & Me.Test_Type & "') AND "

ElseIf Me.cboTestType = "Van" Then
StrWhere = StrWhere & "([Test Type] = '" & Me.Test_Type & "') AND "

Notice the placement of the single quotes- they are inside the double quotes. If you were using a static value in this expression it would look like:
"([Test Type] = 'van'"

If you don't mind, I'd like to show you how you can make your code a little more elegant, but I want to double check something with you before I do- you require all three data points, the TestType, StartDate, and EndDate for your form, correct? The user can't just enter one, they have to provide entries for all three?

[robercs]

Randy, correct the user must enter all three values. Appreciate the help!

[robercs]

The code "kind of" works but it doesn't print the results out by line on the form. It there a limit to the amount of data a Form can hold? Right now when I enter the search criteria and press Go! It will gather the results, i have to scroll through thme using the lfet and right arrow keys. I would like it to print the results directly to the form, but in some cases I amy have several hundred lines....is that too much for the form?

[CreganTur]

I would like it to print the results directly to the form

If you mean that you want the records to look like they do in a table or query, then you need to change your form to show the records in Datasheet view, instead of Form view... but this means you might need to display your records as a subform, since you have buttons and other objects for your users to inteact with on your main form.