Query run on button push with user input (1 Viewer)

[jpl458]

I have a query that runs behind a button that is acting strangely, or I am strange. The SQl is

SELECT Mastertbl3.ID, Mastertbl3.callingnumber, Mastertbl3.companyname1, Mastertbl3.calldate, Mastertbl3.starttime, Mastertbl3.[1stcontact], Mastertbl3.[2ndCtctClass], Mastertbl3.[3rdcontact]
FROM Mastertbl3
WHERE (((Mastertbl3.companyname1)=["Enter Company Name"]))
ORDER BY Mastertbl3.calldate;

The query runs fine on it's own, but when I put behind the button I have to enter the company name 2 times before the data appears into the output box.

Me.QryLbl.Visible = True
Me.QryResulttb.Visible = True
Me.QryResulttb.Height = 7000
Me.QryResulttb.Width = 22100
Me.QryResulttb.RowSourceType = "Table/Query"
Me.QryResulttb.RowSource = "QryCompanybyName"
Me.QryResulttb.ColumnCount = 9
Me.QryResulttb.ColumnWidths = ".4in;.7in;2in;.7in;.8in;1.0in;.8in;.6in;1.5in"

When It gets to the 5th line, it askes for input. And askes again. I've moved the 5th and 6th lines around to different places in the code to see if order makes a difference, but it doesn't, and curious as to why this doesn't work. Do I need to have the data entered into a textbox and have the query reference that for the input? I am using this technique or several other queries that don't ask for input from the user, and they run fine.

 

[Pat Hartman]

Why are you prompting for company name? Why would you not reference a control on the form? The WHERE would probably reference the CompanyID field

WHERE Mastertbl3.companynameID = Forms!myform!CompanyID

 

[pbaldy]

Most of us use forms to gather user input. You have a lot more control, and it would certainly resolve this issue.

 

[jpl458]

Why are you prompting for company name? Why would you not reference a control on the form? The WHERE would probably reference the CompanyID field

WHERE Mastertbl3.companynameID = Forms!myform!CompanyID

ID is not Customer ID, but the row number in the table. The user knows the company name, but probably not the row number. I just want to know why what I tried didn't work. I can put a box on the form where the user enters the company name and reference the text box from the query. But if I could find out why I can't the Enter Company Name in the query it would b helpful.

 

[plog]

When It gets to the 5th line, it askes for input.

That doesn't make sense. The 5th line is:

Me.QryResulttb.RowSourceType = "Table/Query"

You've yet to tell it what query, the 6th line does that. Perhaps you meant 6th and 7th. My guess is that the calls on whatever 2 lines you are referencing both read the query and execute it, thus requiring it to ask for the input twice.

Like others, I agree this method is sub-optimal:

1. A drop down prevents user's guessing at exact spellings--"Plogs Market"/"Plog's Market"/"PLogs Market"/"PlogsMarket".

2. Why load a query instead of just a subform? You an filter a subform with 2 lines of code, instead of your 8.

3. Why not open a report? That you can do in just 1 line of code.

 

[jpl458]

That doesn't make sense. The 5th line is:

Me.QryResulttb.RowSourceType = "Table/Query"

You've yet to tell it what query, the 6th line does that. Perhaps you meant 6th and 7th. My guess is that the calls on whatever 2 lines you are referencing both read the query and execute it, thus requiring it to ask for the input twice.

Like others, I agree this method is sub-optimal:

1. A drop down prevents user's guessing at exact spellings--"Plogs Market"/"Plog's Market"/"PLogs Market"/"PlogsMarket".

2. Why load a query instead of just a subform? You an filter a subform with 2 lines of code, instead of your 8.

3. Why not open a report? That you can do in just 1 line of code.

I've been fooling around with this and discovered this in to code:

Me.QryLbl.Visible = True
Me.QryResulttb.Visible = True
Me.QryResulttb.Height = 7000
Me.QryResulttb.Width = 22100
Me.QryResulttb.RowSourceType = "Table/Query"
Me.QryResulttb.RowSource = "QryCompanybyName"
Me.QryResulttb.ColumnCount = 9
Me.QryResulttb.ColumnWidths = ".4in;.7in;2in;.7in;.8in;1.0in;.8in;.6in;1.5in"

When stepping through the code, when I hit F8 on 5th line, the 6th should show yellow, but it doesn't and opens the msgbox. If I enter the company or not, when I hit F8 again, and go to the 6th Line the message box opens again. If I enter the company name the hit F8 again the data is displayed properly. so the 6th row opens the box on entry and exit.

Looking into reports based on your suggestion. Not familiar with those yet.

 

[Pat Hartman]

Again, I ask -- WHY are you prompting for a value in the query. WHY doesn't the query get what it needs from a form field?

WHERE Mastertbl3.companynameID = Forms!myform!CompanyID

If you don't have the CompanyID on the form (that is a different mystery) then use the Company Name!!!!!

The problem with using the Company Name of course is that it may not be unique.

You are trying to filter the combo but we can't tell what the combo is selecting so we have no clue why you would have Company Name but not CompanyID.

 

[jpl458]

That doesn't make sense. The 5th line is:

Me.QryResulttb.RowSourceType = "Table/Query"

You've yet to tell it what query, the 6th line does that. Perhaps you meant 6th and 7th. My guess is that the calls on whatever 2 lines you are referencing both read the query and execute it, thus requiring it to ask for the input twice.

Like others, I agree this method is sub-optimal:

1. A drop down prevents user's guessing at exact spellings--"Plogs Market"/"Plog's Market"/"PLogs Market"/"PlogsMarket".

2. Why load a query instead of just a subform? You an filter a subform with 2 lines of code, instead of your 8.

3. Why not open a report? That you can do in just 1 line of code.

Again, I ask -- WHY are you prompting for a value in the query. WHY doesn't the query get what it needs from a form field?

WHERE Mastertbl3.companynameID = Forms!myform!CompanyID

If you don't have the CompanyID on the form (that is a different mystery) then use the Company Name!!!!!

The problem with using the Company Name of course is that it may not be unique.

You are trying to filter the combo but we can't tell what the combo is selecting so we have no clue why you would have Company Name but not CompanyID.

I changed it based on the answers. I created a combobox that get its data form a Select Distinct query. The user then clicks on the company name and in the on click event, the data for that particular company is displayed. It works and there is no possibility of typos.

Thanks

 

[Pat Hartman]

excellent choice