Can't make simple loop work

[WalkieTalkie]

Hi
I have a simple loop which I cannot make work - please tell me what I am doing wrong!

I have a list box (lstDate) in which the user selects the dates they want.
lstDate is bound to tblDate and has 4 columns; ID, Date, HolidayName, Outing.
Outing column is Yes/No.
If the user picks a date which is Yes for an outing, the amount they will be charged for the day will be $45. If they choose a day which does not have an outing, they will be charged $40. The aim of the code below is to populate tblTransaction with this info...

Dim varItem As Variant
Dim txtTemp As String
Dim strSQL As String
Dim txtAmount As String


For Each varItem In Me.lstDate.ItemsSelected
txtTemp = txtTemp & Me.lstDate.ItemData(varItem) & ","
txtAmount = IIf(Me.lstDate.Column(3) = True, "-45.00", "-40.00")

Next
txtTemp = Left(txtTemp, Len(txtTemp) - 1)

strSQL = "INSERT INTO tblTransaction ( TransDate, KidAccountID, AttendStatus, TransactionType, Amount ) " & _
"SELECT tblDate.Date, " & Me![KidAccountID] & ", 1 , 11, " & txtAmount & " FROM tblDate " & _
" WHERE ID IN (" & txtTemp & ")"

Debug.Print strSQL  'for troubleshooting
CurrentDb.Execute strSQL, dbFailOnError

The first item works fine, but then the rest just have the same amount as the first, rather than changing according to whether or not it is an Outing day.

I'm still learning this stuff, but I 'm sure this should be relatively simple and I can't work out what I'm doing wrong. Thanks in advance for any help!

 

[Gammachaser]

Ahhh... MultiSelect -- the syntax that keeps on giving.

You are using the right idea in the first line (Me.1stDate.ItemData(varItem)) to get the value of the bound column but on the second line where you calculate txtAmount, you are only referencing the first item in the list... hence the same value. To get the values for the other selections you have to use:

txtAmount = Iif(me.1stDate.Column(3, varItem),"-45.00","-40.00")

This will return the value for the fourth column of each selection (0 based.)

Now for a couple of other issues...

Hard coding your cost values will turn out to be a problem later, I can almost guarantee. The numbers 40 and 45 representing the cost belong in a table so they can be changed later without having to edit code. Another benefit of having them in a table is you could join that table to tblDate in the ListBox record source and make the value the fifth column and eliminate your 'Iif' statement all together by referencing the fifth column for the amount.

Also, you are using what appear to be strings to represent numbers. Even if it suits your purposes here, it is a very bad habit to get into and will also likely cause probelms later. I would also store them as positive numbers and do the proper math as needed (just a personal preference.)

 

[Andrzejek]

just a quick check, should either:

txtAmount = IIf(Me.lstDate.Column(3) = True, "-45.00", "-40.00")

or

txtAmount = Iif(me.1stDate.Column(3, varItem),"-45.00","-40.00")

be

txtAmount = Iif(me.1stDate.Column(3, varItem) [blue]= "Yes",[/blue] "-45.00","-40.00")

"Outing column is Yes/No."

Have fun.

---- Andy

 

[Gammachaser]

Andy --

I left off the '=True' part, but you do not need it. Because the field is Boolean to begin with, it will produce either True or False as a result (Yes or No, if you wish). You could use '= True' or '=Yes' if you want, but it is unnecessary. And if you do use = Yes you can't use = "Yes" just like you can't use = "True" since it is not a string but a logical value. So, Yes = True, but "Yes" <> True. It could be:

txtAmount = Iif(me.1stDate.Column(3, varItem) = True,"-45.00","-40.00")

OR

txtAmount = Iif(me.1stDate.Column(3, varItem) = Yes,"-45.00","-40.00")

but my original code should work just fine.

 

[dhookom]

Isn't the amount field numeric? If so, I would not try to force string values into it [red]"-45.00","-40.00" [/red].

Duane
Hook'D on Access
MS Access MVP

 

[WalkieTalkie]

Thanks Gammachaser, Andy and Duane, for your help and teaching with this. That all makes sense. I will have a go and let you know when I succeed!
I confess I was being lazy by hard-coding the cost values. I have a lookup table with other cost values - when I had this request to add an Outing cost I just wanted to get it done quickly and thought it would be quicker, allowing me to go back later and fix it up. But your reminder, as well as the time I've already spent on it has persuaded me to do it properly the first time.
I will also store the numbers as proper numbers too.
watch this space...
Thanks again
Miranda

 

[WalkieTalkie]

Hi again

I have done as suggested and it hasn't made any difference! I must still be missing something. Here's my code now:

Dim varItem As Variant
Dim txtTemp As String
Dim strSQL As String
Dim txtAmount As Currency


For Each varItem In Me.lstDate.ItemsSelected
txtTemp = txtTemp & Me.lstDate.ItemData(varItem) & ","
txtAmount = Me.lstDate.Column(4, varItem)

Next
txtTemp = Left(txtTemp, Len(txtTemp) - 1)

strSQL = "INSERT INTO tblTransaction ( TransDate, KidAccountID, AttendStatus, TransactionType, Amount ) " & _
"SELECT tblDate.Date, " & Me![KidAccountID] & ", 1 , 11, " & txtAmount & " FROM tblDate " & _
" WHERE ID IN (" & txtTemp & ")"

Debug.Print strSQL  'for troubleshooting
CurrentDb.Execute strSQL, dbFailOnError

Any ideas? Do I need to put something into the 'Next' part of the loop to get it to look for the next value in the list?

 

[Gammachaser]

Well... I guess I concentrated only on the 'MultiSelect' problem and missed the 900lb gorilla in the room.

Try this:

Dim varItem As Variant
Dim strSQL As String
Dim curAmount As Currency
Dim dtTransactionDate as Date

For Each varItem In Me.lstDate.ItemsSelected
  curAmount = Me.lstDate.Column(4, varItem)
  dtTransactionDate = Me.lstDate.Column(1, varItem)

  strSQL = "INSERT INTO tblTransaction (TransDate, KidAccountID, AttendStatus, TransactionType, Amount) " & _
  "Values (" & dtTransactionDate &", " & Me![KidAccountID] & ", 1 , 11, " & curAmount & ")"

  Debug.Print strSQL  'for troubleshooting
  CurrentDb.Execute strSQL, dbFailOnError
Next

That will generate a new record for each selection from the ListBox. Note that the Date now comes from the ListBox instead of the table. You can't mix your data sources. I will try to explain.

What you had before seemed to be a combination of two forms of an Append Query. When doing an Append, you can either select rows from a table (or query) to append onto another table, or you can insert rows using data that you supply. You had a hybrid of both techniques... won't work. You had some data from tables and some data from the form. If that can be done, I would love to be taught, but I don't think it is possible.

The Multiple Record Append is like:

"INSERT INTO tblOne (field1, field2...) SELECT (field1, field2...) FROM tblTwo WHERE [i]yourcriteria[/i]"

The FROM can also be a query, but ALL of the data comes from the table or query. You can't mix in values from the form.

The Single Record Append is what I have corrected your code to do. It has a Fields list for the destination table and a Values List for the source data. The data all comes from sources you supply (such as values in your list box or other Controls on the form. If you want data from a table that isn't on the form, you need to use something like DLookup or a Recordset to get it, then enter it into the SQL text.

The advantage of the Multi-record method is that it does the entry all at once... but all your data has to be in the source table or query. The advantage of the single-record method is flexibility in supplying data... but it is not as quick. In this case, since you are not adding more than a couple of records at a time, you will not notice the time penalty of the single-record method.

My last observation is that your 'hard coded' values for the AttendStatus and TransactionType (1 and 11, respectively) will never change. What is their purpose? If they are always the same, why have them?

OK, I lied about that being the last observation. Having not worked with the Currency data type I may be wrong about this, but I think you are using a sledge hammer to drive thumb tacks. I think a data type of Single would do just fine and that even Integer would do if your cost is always in whole numbers. Or maybe not.

 

[WalkieTalkie]

Thanks for your patience. It is working fine now and I have learned something as well.

Incidentally, I started trying to make it work with a DLookup in the query but couldn't make it work so had given up on it.

In answer to your other observations:
1. the 'hard coded' values for the AttendStatus and TransactionType (1 and 11, respectively); true, they never change. These entries are for bookings in advance. 1 = Pre-booked, and 11 = Childcare Fee. At the time of attendance the user can change the AttendanceStatus and TransactionType as necessary.
2. I've changed the currency type to integer, as you suggest.

Thanks again.

 

[Gammachaser]

You are welcome. The patience comes from having to teach other Instructors. Just like Doctors are the worst patients, Instructors are the worst students.