Microsoft Access 2013 Developent

[InspectorHound]

Hi,
I need to develop an Access application and I have not done this in about 10 years. I am using Microsoft Access 2013 and developong a desktop application since I do not have a SharePoint server. I would like to open with a search form where the user can enter three pieces of information. After he presses the search button, a dataset is returned. From this dataset, the user can select one item and get more detailed information on this item. What kind of form do I use to return the dataset (a query). And in the dataset, do I put in a link so I can call up the detailed information?

Thanks.

 

[SkipVought]

Hi,

Presumably the user enters 3 pieces of information that is used to query your database and return a resultset.

Given a row selection within the resultset, where would "more detailed information" come from?

 

[dhookom]

I would use a continuous form with code that uses the values entered into the controls to build a record source SQL statement for the form. If you have questions, please come back with:
- names of the three controls
- current SQL view of the record source
- field names and data types used for the three controls

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

Ok, let me start from the beginning and ask one question at a time. I want to create a search form where the user can search on Last Name, SSN, or Auth ID. The user can search on one or all of those values. When the user enters a last name, say Taylor, this value is sent to a query so that all records with that last name are returned. In the criteria field of the query, I have Like "*" & [Forms]![SearchTest]![txtLastName] & "*" for all three search fields, with the appropriate text box name. I then do an OpenQuery command to return five pieces of information from that same query. How should I return this data.
######
Public Sub cmdSearch_Click()
DoCmd.OpenQuery "QueryTest", acViewNormal, acReadOnly
Me.txtLastName = " "
Me.txtSSN = " "

MsgBox " Hello "
End Sub
####
One problem that I do have is that the text boxes are not cleared and every time I open the form and type in a new name, the old name is still returned.

Thanks,

 

[dhookom]

As I stated, I would place the search text boxes in the header of a continous form and remove any dynamic criteria from QueryTest. The click event of cmdSearch might look something like:

Dim strSQL as String
strSQL = "SELECT * FROM QueryTest WHERE 1 = 1 " 
If Not IsNull(Me.txtLastName) Then
    strSQL = strSQL & " AND [LastNameField] Like ""*" & Me.txtLastName & "*"" "
End If
If Not IsNull(Me.txtSSN) Then
    strSQL = strSQL & " AND [SSNField] Like ""*" & Me.txtSSN& "*"" "
End If
[COLOR=#4E9A06]'  etc for other controls[/color]
Me.RecordSource = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

Ok. So then in the form header, I would have three unbound controls for the search criteria and in the form detail, I would have bound controls. Is that what you are saying?

 

[dhookom]

Yes the detail section would have text boxes bound to fields in QueryTest.

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

Ok, but now nothing populates.I mean, the searhc criteria is supposed to return more than one row. do I need a special format for the detail section. Adn do I still use my OpenQuery code?

 

[InspectorHound]

And do I set the control source for the detail textboxes programmatically.I tried to set them in the properties section and that does not work.

Thank so much.

 

[dhookom]

Debug/trouble-shoot your code so you can determine the value of strSQL. If you aren't familiar with debugging code, check out faq705-7148.

The control sources of all of the text boxes in the detail section should have the name of a field from the record source.

When you state "I tried to set them in the properties section and that does not work." you need to provide us with a little more detail. We don't understand "does not work" without you being our eyes into what you are seeing.

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

Yes, I understand that the control source of the text boxes should be bound. Do I set this in the code? I was setting the control source in the property section and I think this was the problem. And would I set ti to QueryName [FieldName]?

Also, when I press the Search command button, more than one record should be returned. How do I work this? It should return about 10 to 20 records, based on the criteria.

Thanks.

 

[dhookom]

The form must have a record source and the text boxes in the detail section must have control sources set to fields from the record source. This is all done in design view. Can you confirm this is how your form is set up?

What is the value of strSQL at the end of your code?

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

My string seems to be correct:
SELECT * FROM qryUserAuth WHERE 1=1 AND [traveler last name] Like "*Ying*"
AND [traveler SSN] Like "**"
AND 'LinkedProfleID] like "**"

Right now, I have set the record source in the PROPERTIES, which seems to be incorrect since when I open the form, the detail section is already populated.
I have attached a screen shot of my form. The form is based on one query that has five fields. Three search fields are displayed in the header and all five fields are displayed in the detail. I think my prob;lem is at what point do I set me record source propery in the code you gave me earlier. Also, I would like to display more than one record in the detail section.

Thanks.

 

[dhookom]

What do you see when you paste the SQL string into the SQL view of a blank query?

The three "search fields" aren't "fields", they are unbound text boxes.

"at what point do I set me record source propery" ... "after he presses the search button"

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

Hi,

I've created a tabular form for the detail section, but sill need to know the best place to put the three search fields. Shold they remain in the header. Will you have time today to answer?

Thanks.

 

[dhookom]

Again your "three search fields" are three unbound text box controls and they should be in the form header section. Your form Default View should be set to "Continuous Forms".

This answer should be today as long as you are in nearly the same time zone.


Duane
Hook'D on Access
MS Access MVP

 

[MajP]

If you put it in the detail section you would end up duplicating the three controls for each record. It would still work, but look ugly and be confusing. The footer is also viable location if it is logical to the user.

 

[InspectorHound]

Ok, how do I set the control source for the items in the detail section?

 

[InspectorHound]

Ok, I have this in my code:
########################################################
Public Sub cmdSearch_Click()

Dim strSQL As String

strSQL = "SELECT * FROM qryUserAuth WHERE 1 = 1 "

If Not IsNull(Me.txtLastName) Then
strSQL = strSQL & " AND [traveler last name] Like "" * " & Me.txtLastName & " * """
End If

If Not IsNull(Me.txtSSN) Then
strSQL = strSQL & " AND [traveler SSN] Like ""*" & Me.txtSSN & "*"" "
End If

If Not IsNull(Me.txtAuth) Then
strSQL = strSQL & " AND [LinkedProfileID] Like ""*" & Me.txtAuth & "*"" "
End If

' etc for other controls
Me.RecordSource = strSQL

Me.txtLastName = " "
Me.txtSSN = " "
Me.txtAuth = " "
MsgBox " Hello "
End Sub
#########################################################
When I do click the search button, nothing happens. What should I add to my code to make the search button return the records to the detail section?

 

[dhookom]

Figure out how to use the Code TMGL tag for your posts. Get rid of a space after Me.txtLastName & ".

Public Sub cmdSearch_Click()
    Dim strSQL As String
    strSQL = "SELECT * FROM qryUserAuth WHERE 1 = 1 "
    If Not IsNull(Me.txtLastName) Then
        strSQL = strSQL & " AND [traveler last name] Like "" * " & Me.txtLastName & "* """
    End If
    If Not IsNull(Me.txtSSN) Then
        strSQL = strSQL & " AND [traveler SSN] Like ""*" & Me.txtSSN & "*"" "
    End If
    If Not IsNull(Me.txtAuth) Then
        strSQL = strSQL & " AND [LinkedProfileID] Like ""*" & Me.txtAuth & "*"" "
    End If
    [COLOR=#4E9A06]' etc for other controls[/color]
    Me.RecordSource = strSQL
    Debug.Print strSQL  [COLOR=#4E9A06]'check the debug window for this value[/color]
    Me.txtLastName = Null
    Me.txtSSN = Null
    Me.txtAuth = Null
    MsgBox " Hello "
End Sub

If this doesn't provide the clues you need then please provide the SQL view of qryUserAuth.

Duane
Hook'D on Access
MS Access MVP