473,325 Members | 2,870 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Auto Update of Textbox

cbucks
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
5 3569
NeoPa
32,556 Expert Mod 16PB
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
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
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
32,556 Expert Mod 16PB
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
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

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

Similar topics

0
by: Alex Moskalyuk | last post by:
I am using System.Windows.Forms.TextBox to publish some real-time data that's coming off the external devices. My data is fed into the TextBox with proper precautions not to exceed the MaxSize...
0
by: george d lake | last post by:
Hi, Is there a way to have a "Auto Complete" textbox or a "editable" dropdown? Here is my problem. I have a screen that need to have a list of 800+ employees. To be a dropdown, that could be a...
6
by: kurt | last post by:
Hi all, I have something that has me stumped. I have a webform that has several text boxes. Each of these boxes has the textchanged set so when the user enters text and leaves the textbox, it...
0
by: 23s | last post by:
Is there any way I can send a vertical value to a form's scroll position? I have a full-screen form that, at launch, contains an empty tab sheet. At run time, the user can dynamically append a...
0
by: sdash | last post by:
I'm working on a simple formview screen that should update a SQL Server 2000 record. I'm sure there must be something simple wrong, but when I press update, the screen refreshes and the changes...
0
by: saraDotNet | last post by:
Hi, I'v Two Questions: 1-Is there a way to make an auto numbering avalible in vb.net form textbox as the one in Access db columns when we set the datatype as autonumbering?? 2- I'm working on a...
3
by: TS | last post by:
I am using IE 7. I have a website running on my local machine (localhost) and auto complete doesnt work for any of the textboxes, but going to web sites on the internet does support this so i know...
4
by: bnono | last post by:
Hi all, I have to use a Fortran DLL inside a VBA code in MS Access 2003. However the DLL takes quite a long time to run, and I would like it to echo a few messages while in progress. I thought...
2
by: sirdavethebrave | last post by:
Hi guys - I have written a form, and a stored procedure to update the said form. It really is as simple as that. A user can go into the form, update some fields and hit the update button to...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.