Show current record value from table in combobox in addition to the query results

[saad749]

I have been looking for solutions and workarounds for this for a week now.
I have a table, with a related value in another table. E.g. A Items table with a batch value from another table.
And

I have a form to enter how many of these items has been used and from which batch number they belong.
The batch number is from a dropdown, and batches can be finished(exhausted) and marked such in the table so they no more show in the dropdown.

All this works fine, until, I go back to a entry which was from a batch that has been finished. The combobox is empty although the (Already finished) batch number is mentioned in the table. This is perfectly normal as my query for the combobox is :

SELECT ItemBatch.ItemId, ItemBatch.ItemBatchNumber, ItemBatch.Finished, ItemBatch.ItemName 
FROM ItemBatch 
WHERE (((ItemBatch.Finished)=False) 
AND ((ItemBatch.ItemName)=[Forms]![ItemMasterForm]![ItemDataSheet].[Form]![ItemName]));

What I want is to show the current batch number as well. I tried to make this query get the current value, but wasn't successful. I tried to make a calculated field based on the dropdown and show its value, but it didnt helped out as well.


Is there any way I can show the batch number in the datasheet? I have to use a datasheet and not a form, because there will be many sub records for the main form, and having a form will be very uneasy.
Any help, guidance in this regard would be highly appreciated.

[ItsMe]

...I tried to make a calculated field based on the dropdown and show its value, but it didnt helped out as well....

I am not familiar with calculated fields at the table level so I cannot explain why they may or may not work within a query.

As for displaying the current record's ID or batch number that a User may have selected for viewing. You would simply include the form name and control name within your query. You can create an alias that has a dynamic parameter based on the control that is bound to the desired field, i.e. batch number.

Every time you run the query, the bound control's value will be the Current Record's value for the field. The alias within the query will retrieve that value.

It seems that a major hurdle for you is you require DataSheet view instead of FormView. Determining the CurrentRecord while in DataSheet view is not always straightforward. If you click a Row while in DataSheet view, that Row will be the Current Record. If you click somewhere else, like a combo or a Command Button, there will not be a Current Record.

Perhaps your solution is to employ an unbound control in the footer, header, or Main Form. An unbound control can have an expression that causes the Value of the Unbound Control to equal the last record selected.

Maybe something like
=Me.BatchNumber.Value

or you might need to include a subform name

Then, use the name of your unbound control within your dynamic parameterized query.

[saad749]

I am not familiar with calculated fields at the table level so I cannot explain why they may or may not work within a query.

As for displaying the current record's ID or batch number that a User may have selected for viewing. You would simply include the form name and control name within your query. You can create an alias that has a dynamic parameter based on the control that is bound to the desired field, i.e. batch number.

Every time you run the query, the bound control's value will be the Current Record's value for the field. The alias within the query will retrieve that value.

It seems that a major hurdle for you is you require DataSheet view instead of FormView. Determining the CurrentRecord while in DataSheet view is not always straightforward. If you click a Row while in DataSheet view, that Row will be the Current Record. If you click somewhere else, like a combo or a Command Button, there will not be a Current Record.

Perhaps your solution is to employ an unbound control in the footer, header, or Main Form. An unbound control can have an expression that causes the Value of the Unbound Control to equal the last record selected.

Maybe something like
=Me.BatchNumber.Value

or you might need to include a subform name

Then, use the name of your unbound control within your dynamic parameterized query.

The problem may be I guess with the last suggestion is that the dataview will have many item records of different products with each having different batch number. How can I address that.

The simplest way I guess is to someway have a Union clause which will select the current value of the record from the table. But.. as u mentioned, selecting current record is the main issue, esp in the row source query.

[ItsMe]

...The problem may be I guess with the last suggestion is that the dataview will have many item records of different products with each having different batch number. How can I address that...

The only way I can imagine is to keep track of the record that has focus. You will need to train the User to click a record in order to set focus on the record (Current Record). My suggestion in the previous post is to use an expression in an unbound TextBox. This alone, may not be enough to keep track of which record has focus. It depends on where the unbound control is in relation to the datasheet view. Perhaps you can use Continuous Form and a Header/Footer. Or, maybe, you need a Main form Subform with DataSheet view. If the latter, you may require some VBA in the subform object to trigger the On Current of that form.