By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,273 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Auto Update of Textbox

cbucks
P: 4
I would really appreciate help with this one. The string of code below fires with the afterupdate function of a 3rd combo box slelction. The idea is to provide a series of dynamic text boxes. The combination of these selections will power the update of this last "Form_BB_Value" textbox. I'm building an Access 2000 db in Access 2003.

Private Sub System_AfterUpdate()
On Error Resume Next
Form_BB_Value = "Select tblCRX.BB_Value " & _
"FROM tblCRX " & _
"WHERE tblCRX.Type= Asset_Type.Value And tblCRX.Mfg = Manufacturer.Value And tblCRX.Model = System.Value " & _
"ORDER BY tblCRX.BB;" '
End Sub
Dec 5 '06 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,615
Try :
Expand|Select|Wrap|Line Numbers
  1. Private Sub System_AfterUpdate()
  2.     Dim strSQL As String
  3.  
  4.     On Error Resume Next
  5.     strSQL = "(([Type]='" & [Asset_Type] & _
  6.              "') And ([Mfg]='" & [Manufacturer] & _
  7.              "') And ([Model]='" & System & "'))"
  8.     Form_BB_Value = DLookup("[BB_Value]", "[tblCRX]",strSQL)
  9. End Sub
This assumes that you are actually talking about a TextBox (as stated) and not a ComboBox or ListBox.
Dec 5 '06 #2

cbucks
P: 4
NeoPA:

First off, I truly appreciate your help. I've been trying to figure this one out on my own for 5 days now. Even with the code you suggest below, I get one of the same error messages I've encountered. "You can't assign a value to this object" "run-time error '-2147352567 (80020009)':"

Any thoughts? The BB_Value is formatted as Currency in tblCRX and I am indeed trying to place the results into a Textbox.

Try :
Expand|Select|Wrap|Line Numbers
  1. Private Sub System_AfterUpdate()
  2.     Dim strSQL As String
  3.  
  4.     On Error Resume Next
  5.     strSQL = "(([Type]='" & [Asset_Type] & _
  6.              "') And ([Mfg]='" & [Manufacturer] & _
  7.              "') And ([Model]='" & System & "'))"
  8.     Form_BB_Value = DLookup("[BB_Value]", "[tblCRX]",strSQL)
  9. End Sub
This assumes that you are actually talking about a TextBox (as stated) and not a ComboBox or ListBox.
Dec 5 '06 #3

cbucks
P: 4
Not sure why, but after playing around with this code some more, it worked! This is why I continue to learn on my own. It's like a kid and a cookie jar. Keep working hard and you'll get a treat.

Thanks to everyone for contributing to the site!
Dec 6 '06 #4

NeoPa
Expert Mod 15k+
P: 31,615
CBucks,

Glad to hear you got it working.
Why don't you post your working code.
It will be interesting for anyone reviewing this thread at any time.
Dec 6 '06 #5

cbucks
P: 4
This allows user to select choices from 3 dynamic rowsources (comboboxes). Selection from box A, drives selections in Box B, then combination of selections in Boxes, A & B, drive selection of choices for Box C. Results from A, B, And C, crossreference data in 4 column table (sorry I think in Xcel) and autoplace a value in TextBox 1. Very useful tool.

Code is below and example of datatable is below that.


Option Compare Database

Expand|Select|Wrap|Line Numbers
  1. 'Code for Combobox B. (selection for rowsource A, taken directly from table)
  2. Private Sub Asset_Type_AfterUpdate()
  3.     On Error Resume Next
  4.     Manufacturer.RowSource = "Select DISTINCT tblCRX.Mfg " & _
  5.         "FROM tblCRX " & _
  6.         "WHERE tblCRX.Type= Asset_Type.Value " & _
  7.         "ORDER BY tblCRX.Mfg;"
  8. End Sub
Expand|Select|Wrap|Line Numbers
  1. 'Code for Combobox C.
  2. Private Sub Manufacturer_AfterUpdate()
  3.     On Error Resume Next
  4.     System.RowSource = "Select DISTINCT tblCRX.Model " & _
  5.         "FROM tblCRX " & _
  6.         "WHERE tblCRX.Type= Asset_Type.Value And tblCRX.Mfg = Manufacturer.Value " & _
  7.         "ORDER BY tblCRX.Model;"
  8. End Sub
Expand|Select|Wrap|Line Numbers
  1. 'Code for TextBox 1.
  2. Private Sub System_AfterUpdate()
  3.     On Error Resume Next
  4.     strSQL = "(([Type]='" & [Asset_Type] & _
  5.              "') And ([Mfg]='" & [Manufacturer] & _
  6.              "') And ([Model]='" & System & "'))"
  7.     Form_BB_Value = DLookup("[BB_Value]", "[tblCRX]", strSQL)
  8. End Sub

Table Example

Column A) Type (CD, VHS, Book)
Column B) Mfg (Sony, Paramount, etc)
Column C) Title (Hair, Led Zeppin, Cars, etc)
Column D) Price ($$$)

Benefit is table values can be updated on a regular basis and almost completly removes the possibility of user error once appropriate additional locks are placed on controls.
Dec 7 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.