Accessing rows in subform

[Krij]

Hi!

I want to access individual rows in a subform (frmOrderdetails (datasheet view)).

The reason for this is that I have a checkbox at the end of each orderline, and user will be able to check individual lines and do a return of these lines.

For now, I have a query to return lines, but the problem is that when there are two lines checked, it'll only return the first one.

Anybody that knows how to obtain this?



Thank you!

 

[Pat Hartman]

Is the checkbox bound?
You'll need to post your query.

 

[Krij]

Hi!

Here's the query:

strPutBack = "INSERT INTO tblAmountItems(ItemID,Amount,MyDate,Returned,OrderID) " _
& "SELECT ItemID,Amount,Now(),Returned,OrderID " _
& "FROM tblOrderdetails " _
& "WHERE tblOrderdetails.ItemID = " & Forms!frmOrders!frmOrderdetails!ItemID _
& "AND tblOrderdetails.OrderID = " & Forms!frmOrders!frmOrderdetails!OrderID _
& "AND tblOrderdetails.Returned = True;"

CurrentDb.Execute strPutBack, dbFailOnError

Yes the checkbox is bound to tblOrderdetails. As I said: the query works fine as long as I return one ItemID or two or more ItemID that has the same ItemID. When two different ItemID the query only returns one ItemID.

Is the checkbox bound?
You'll need to post your query.

 

[Pat Hartman]

There's a space between Order and ID on the first line of the SQL. I'm assuming that that is a typo since you said the query works.

The query will never insert different ItemIDs. You have selection criteria that prevents it.

& "WHERE tblOrderdetails.ItemID = " & Forms!frmOrders!frmOrderdetails!ItemID _

This criteria should probably be removed. I think that you think that it is scrolling through the rows of the form's RecordSource. It is not. It is referencing ONLY the current row which is the first visible row unless you have moved the record pointer.

 

[Krij]

Hi!

Yes, you're right. That's a typo. However... how is access supposed to know where to insert the ItemID if I don't tell which ItemID to insert where...?

I think it should be possible to use a for..each statement here or...?

Thank you for answering

There's a space between Order and ID on the first line of the SQL. I'm assuming that that is a typo since you said the query works.

The query will never insert different ItemIDs. You have selection criteria that prevents it.
This criteria should probably be removed. I think that you think that it is scrolling through the rows of the form's RecordSource. It is not. It is referencing ONLY the current row which is the first visible row unless you have moved the record pointer.

 

[Pat Hartman]

You don't need a loop here. The select part of the SQL statement will select all returned items for the order once you remove the ItemID criteria. And the insert part will append them to the other table.

 

[Krij]

You don't need a loop here. The select part of the SQL statement will select all returned items for the order once you remove the ItemID criteria. And the insert part will append them to the other table.

Thank you so much for patience and guidance. That did the trick. I was so convinced that I really needed the ItemID.