Problem filling Recordset 2

[dazbc]

Ok - so the problem 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.... Maybe its a problem reading the values that are selected, in my program, from the comboboxes... im not too sure.

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

 

[ThomasLafferty]

Take a close look at the number of apostrophes and quotation marks you have used and make sure that you have a matching set for every intended pair.

I color coded what I thought were the matching pairs, and I noticed that you don't appear to have a closing double quote for the initial opening quote here:

([!]"[/!]SELECT BillingMonths

Look over the color coordinated sets and see if you notice the same thing.

Set VATQuerySet = CurrentDb.OpenRecordset([COLOR=purple][b]"[/b][/color]SELECT BillingMonths.[Total Excluding VAT] FROM SimsUpdate INNER JOIN BillingMonths ON SimsUpdate.GSM = BillingMonths.GSM WHERE BillingMonths.[Month] = [COLOR=blue]'[/color] & strMonth & [COLOR=blue]'[/color] AND BillingMonths.[Year] =  [COLOR=green]'[/color][COLOR=purple]"[/color] & intYear & [COLOR=purple]"[/color][COLOR=green]'[/color] AND SimsUpdate.[Employee Name] = [COLOR=red]'[/color][COLOR=green]"[/color] & strEmployee & [COLOR=red]"[/color][COLOR=green]'[/color][COLOR=red]"[/color])

My eyes could be deceiving me, and if all your apostrophes and quotes don't match, you should be getting an error.

Your thoughts?

Tom

Live once die twice; live twice die once.

 

[dazbc]

thanks, but I wish that was the solution, there are no errors when it comes to quotation marks etc... im nearly sure it has something to do from reading the values selected from the comboboxes, im not too sure if im going about it the correct manner

 

[ThomasLafferty]

Hmmm...try this:'

strMonth = Me.MonthCombo[!].Value[/!]
intYear = Me.YearCombo[!].Value[/!]
strEmployee = Me.EmployeeCombo[!].Value[/!]

Hope this is it...

Tom

Live once die twice; live twice die once.

 

[TheAceMan1]

How are ya dazbc . . .

Here's your code with SQL corrections:

[blue]   Dim strEuro As String, VATQuerySet As DAO.Recordset, SQL As String
   
   strEuro = "€"
   strMonth = Me.MonthCombo
   intYear = Me.YearCombo
   strEmployee = Me.EmployeeCombo
   SQL = "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 & "')"
   
   Set VATQuerySet = CurrentDb.OpenRecordset(SQL)
   Me.TotalExcludingVATTextbox.SetFocus
   VATQuerySet.MoveFirst
   
   [red]Me.TotalExcludingVATTextbox.Text = strEuro [b]+ VATQuerySet.Fields[/b][/red][/blue]

However you'll have to explain what your attempting to do in [red]red[/red] . . .

See Ya! . . . . . .

 

[PHV]

And get rid of single quotes for each field defined as numeric (likely Month and Year)

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

 

[dazbc]

Great, that seems to work, now, what im trying to do is fill the Total ExcludingVat Textbox with a euro sign (DONE) and the value that results from the query - how do i do that??

Me.TotalExcludingVATTextbox.Text = strEuro + VATQuerySet.Fields

that code is what im trying but it obviously doesnt work - any ideas?
(thank you aceman1 and PHV)

 

[dazbc]

Its ok, I found out what the problem was, tis all working now! thanks guys for your help, very much appreciated!