Populating Unbound Textbox on Form (1 Viewer)

robbrgr · Dec 11, 2017

I need help with an Access form. When I use the code below, my unbound text box is being populated on all records of a form, instead of the one currently being used. How do I have the text box populated on only the current record? Thanks.

Private Sub Item_Budget_Name_ID_Change()
Dim rst As DAO.Recordset
Dim BudgetSingleAmount As Double

Dim SQL As String

SQL = "SELECT [Item Budget Single Amount] " & _
"FROM [Item Budget Amount Table] " & _
"WHERE [Item Budget Amount Table].[Month Name ID] = 16 " & _
"AND [Item Budget Amount Table].[Item Budget Name ID] = " & [Form_Test Internet Order Items Form].[Item Budget Name ID].Value & ";"

Set rst = CurrentDb.OpenRecordset(SQL)
BudgetSingleAmount = rst![Item Budget Single Amount]
rst.Close
Set rst = Nothing
[Form_Test Internet Order Items Form].[Item Budget Single Amount].Value = BudgetSingleAmount
End Sub

pbaldy · Dec 11, 2017

An unbound textbox will do that. Since the value isn't associated with a record, it is simply displayed until told otherwise. You can clear it in the current event of the form, but if the form is in continuous or datasheet view you will still see the same thing on every record.

Solo712 · Dec 11, 2017

robbrgr said:
I need help with an Access form. When I use the code below, my unbound text box is being populated on all records of a form, instead of the one currently being used. How do I have the text box populated on only the current record? Thanks.

Private Sub Item_Budget_Name_ID_Change()
Dim rst As DAO.Recordset
Dim BudgetSingleAmount As Double

Dim SQL As String

SQL = "SELECT [Item Budget Single Amount] " & _
"FROM [Item Budget Amount Table] " & _
"WHERE [Item Budget Amount Table].[Month Name ID] = 16 " & _
"AND [Item Budget Amount Table].[Item Budget Name ID] = " & [Form_Test Internet Order Items Form].[Item Budget Name ID].Value & ";"

Set rst = CurrentDb.OpenRecordset(SQL)
BudgetSingleAmount = rst![Item Budget Single Amount]
rst.Close
Set rst = Nothing
[Form_Test Internet Order Items Form].[Item Budget Single Amount].Value = BudgetSingleAmount
End Sub

Paul is correct. You will not be able to get the unbound textbox carry a value dependent on the current row. The quickest fix would be to redefine the field as "calculated" bound field if you use A2010 or later.

Best,
Jiri

Mark_ · Dec 11, 2017

This also looks like it should be done in the query underlying the form. Look into how to JOIN tables in a query.

NOTE: If [Item Budget Name ID] is unique (and having ID in there tends to make one think it as auto number / primary key) then you would not need to worry about [Month Name ID].

IF you are NOT looking this up by a primary key I'd recommend re-evaluating how you are doing your lookup.

Populating Unbound Textbox on Form (1 Viewer)

robbrgr

New member

pbaldy

Wino Moderator

Solo712

Registered User.

Mark_

Longboard on the internet

Similar threads

Users who are viewing this thread