SQL/Combobox problem - filling a textbox

[dazbc]

I have 3 comboboxes - Month Year and Employee

I need to match the user chosen month, year and employee to the database and print the employees VAT amount in a text box

Below is some of the code and the query I am having problems with (the query gives error: too few parameters - expected 4

can anyone help?

Private Sub MonthCombo_Click()

strMonth = MonthCombo.Text

End Sub

'the same "combo clicks are also done for Year and Employee

Private Sub EnterDate_Click()
Dim VATQuerySet As DAO.Recordset

Set VATQuerySet = CurrentDb.OpenRecordset("SELECT BillingMonths.[Total Excluding VAT] FROM SimsUpdate INNER JOIN BillingMonths ON SimsUpdate.GSM=BillingMonths.GSM WHERE BillingMonths.[Month] = strMonth AND BillingMonths.[Year] = strYear and SimsUpdate.[EmployeeName] = strEmployee")

End Sub

'i have the query all on one line so next line with '_' is
'not the problem

 

[CautionMP]

dazbc,
Does the underlying query need 4 parameteres? Here is a way to test, add this to your code so you can see the SQL of the query your trying to open. Once the code stops copy the SQL and paste into a QBE:SQL window and run the query to see if it still asks for 4 parameters.

...
Dim sqlVATQuerySet As String
...
sqlVATQuerySet = "SELECT BillingMonths.[Total Excluding VAT] FROM SimsUpdate " & _
                 "INNER JOIN BillingMonths ON SimsUpdate.GSM=BillingMonths.GSM " & _
                 "WHERE BillingMonths.[Month]=" &  strMonth & _
                 "AND BillingMonths.[Year]=" &  strYear &
                 "AND SimsUpdate.[EmployeeName]=" &  strEmployee & ";"
Debug.Print sqlVATQuerySet
Stop
[highlight grey]Set VATQuerySet = CurrentDb.OpenRecordset(sqlVATQuerySet)[/highlight]

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[Remou]

I think you will find that strMonth etc are empty. I think it would be better to refer to the combo boxes just before the sql:
[tt]strMonth = Me.MonthCombo
'and so on for the other boxes
Set VATQuerySet = CurrentDb.OpenRecordset("SELECT BillingMonths.[Total Excluding VAT] FROM SimsUpdate INNER JOIN BillingMonths ON SimsUpdate.GSM=BillingMonths.GSM WHERE BillingMonths.[Month] = " & strMonth & " AND BillingMonths.[Year] = " & strYear & " and SimsUpdate.[EmployeeName] = '" & strEmployee & "'")[/tt]

You say strYear and strMonth, but I suspect these are numeric.

 

[dazbc]

Remou

I tried what you have told me to do

Yes, strYear should have been numeric but I still have an error with the query...

"too few parameters - expected 2"

at least the expected has gone from 4 down to 2

Can you help me a little more??

 

[Remou]

I think that one of the best ways of testing SQL is with the query design screen. If you change your code a little:

strSQL="SELECT BillingMonths.[Total Excluding VAT] FROM SimsUpdate INNER JOIN BillingMonths ON SimsUpdate.GSM=BillingMonths.GSM WHERE BillingMonths.[Month] = " & strMonth & " AND BillingMonths.[Year] = " & strYear & " and SimsUpdate.[EmployeeName] = '" & strEmployee & "'"
'This line prints to the immediate window
Debug.Print strSQL
Set VATQuerySet = CurrentDb.OpenRecordset(strSQL)

You will be able to see from the immediate window if there are any gaps which need to be corrected and you will also have an SQL statement that can be pasted into the SQL view of the query design window. Don't forget that text fields need single quotes, as shown with EmployeeName.

 

[dazbc]

I did that but it didnt really help me, am still down to "expected 2 parameters".... I honestly cant see where its going wrong

 

[dazbc]

Ok - so the problem now is that the recordset that I have created is not filling, or entering any value into it, CAN ANYONE HELP as this is the very last part of my program....

here is the subsequent code ->

Dim strEuro As String
Dim VATQuerySet As DAO.Recordset

strEuro = "€"
strMonth = Me.MonthCombo
intYear = Me.YearCombo
strEmployee = Me.EmployeeCombo


Set VATQuerySet = CurrentDb.OpenRecordset("SELECT BillingMonths.[Total Excluding VAT] FROM SimsUpdate INNER JOIN BillingMonths ON SimsUpdate.GSM = BillingMonths.GSM WHERE BillingMonths.[Month] = ' & strMonth & ' AND BillingMonths.[Year] = '" & intYear & "' AND SimsUpdate.[Employee Name] = '" & strEmployee & "'")
Me.TotalExcludingVATTextbox.SetFocus
VATQuerySet.MoveFirst

Me.TotalExcludingVATTextbox.Text = strEuro + VATQuerySet.Fields

 

[PHV]

If Year is defined as numeric in BillingMonths:

Set VATQuerySet = CurrentDb.OpenRecordset("SELECT B.[Total Excluding VAT]" _
 & " FROM SimsUpdate AS S INNER JOIN BillingMonths AS B ON S.GSM=B.GSM" _
 & " WHERE B.Month='" & strMonth & "' AND B.Year=" & intYear _
 & " AND S.[Employee Name]='" & strEmployee & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SuryaF]

When you get that error just take the query text (the value of the strSQL) and put it into a new query and try to run it. It will ask for those parameters, this way you can identify them.

 

[Zion7]

Your concatenation was incorrect, in your original post.
Sorry Caution, but your lacking spaces.
According to the variables Remou,
your literals may be incorrect.

PHV I assume has it, but I'm a little leary that month is a string.

dazbc what are the data types of B.Month & B.Year?

I would assume integer, but you're using string variables?

Clarify this, and you may have solved your problem.