User input query help

[Stew]

Before I ask my question I want to thank you for all your prompt help. Without this board ny progress would be a fraction of what it is now on my current assignment. I'm not sure if this is the best way to do this, if you know a better way please suggest it. I have a form with a user input spot. I have a query with that input set as the criteria for one field. On the form I have a run query button and a subform with the query output. I would like the popup input message not to come up when I first open the form. Any suggestions would be greatly appreciated on the best way to format this page.

[HansV]

I'm not sure why you'd get a prompt when you open the form - I'd have to see (a stripped down, compacted and zipped copy of) the database for that.

You may be able to get around it by clearing the Record Source property of the subform in design view, and by setting this property to the name of the query in the After Update event of the text box used for the input - something like this, with the appropriate names substituted:

Code: Select all

Private Sub txtInput_AfterUpdate()
  If IsNull(Me.txtInput) Then
    Me.NameOfTheSubform.Form.RecordSource = ""
  Else
    Me.NameOfTheSubform.Form.RecordSource = "NameOfTheQuery"
  End If
End Sub

Here, NameOfTheSubform is the name of the subform as a control on the main form; this is not necessarily the same as the name of the subform in the database window/navigation pane. Open the main form in design view and click once on the subform to see its name in the Properties pane.

[Stew]

Could you post an real simple example of this if possible. I kep getting different error messages when I try and mess around with it. I think the box comes up because the subform is the query output. So it is automatically attempting to run the query. I can't post code do to the block the military has on websites. To access this site I am using my phone.

[HansV]

I'm sorry, I don't really understand the problem you describe, so I'm not sure what I should post. Is there any way you could post a stripped down, compacted and zipped copy of your database (without any sensitive data, of course)? Nannette Padgett manages to do so...

[Stew]

I am currently waiting for a website waiver to go through, and my personal computer is currently destroyed. Ill try and explain better what I am doing. I have a form with two things in it. A text box for input into a query, the other is a subform based on the query output. I want all records to show in the subform, except when a name is entered. Then show only the people with that name. I think I may be doing this the hard way. I will try and get on someone elses computer tonight and post excatly what my problem is.

[HansV]

Let's say that your main form is named frmMain, and that the text box is named txtInput.

Open the query in design view.
Change the criteria for the relevant column to

[Forms]![frmMain]![txtInput] Or [Forms]![frmMain]![txtInput] Is Null

substituting the real names of the form and text box.
Save the query.
Does that work better?

[Stew]

Here is the sample database. It really isn't formated good, I'm just trying to get the query output to work right. I'm showing the form with a subform and output on the form with the same information. What I am trying to do is either have the search query work with the subform, or have the form filter for the search. I'd prefer to get the subform route working, due to the face I can use that fix easier in other parts of the database. Thanks in advance. I'm sorry about the crappy formating on the form. I was just trying two things at once, trying to determine a way to conquer this problem. It is one of my last big hurdles to bring my database together.

[HansV]

The query qryS1Edit has a parameter Las that doesn't refer to anything. This causes the input prompt.

But you don't need this query. Simply set the record source of the subform to tblMain in design view.

The subform is currently bound to the main form on the SSN field. That means the subform will display the same record as the main form. You should set the Link Master Fields property of the subform on the main form to s1editinput and the Link Child Fields property to Last.

You then only need the following code to requery the subform when the text box is updated:

Code: Select all

Private Sub s1editinput_AfterUpdate()
  Me.subfrmS1Edit.Requery
End Sub

See the attached version. Unfortunately, I was not able to compact the database since it has been created in Access 2010.

Sample Problem1.zip

[Stew]

Thanks a lot Hans that helped a lot. I didn't understand the relationship between the subforms and parent forms. That is excatly what I needed. As always you help improve my knowledge.

[Stew]

Following up on this question, can you use wildcards '*' when you like subforms like this?

[HansV]

If you set the Link Master Fields and Link Child Fields properties of a subform, you can't use wildcards - it's always a straight "equals" match.
So you must clear those properties and set the record source of the subform to a query based on tblMain, with the following condition for the Last field:

Like [Forms]![frmS1Edit]![s1editinput]

The rest can remain unchanged. You can then use wildcards in the text box, e.g enter B* to get all names starting with B, or *w* to get all names containing a w.

[Stew]

Works like a charm yet again, thanks. That what I was trying to do at first but I couldn't get it to work right.