Solved

textbox control source

Posted on 2002-06-20
15
251 Views
Last Modified: 2012-08-13
On my form I have a listbox (pulldown) and a textbox.  The listbox is table/query , and it selects all 'number' fields from table main.

Once one of the numbers has been selected, I want the textbox to automatically update its value.  The value should come from table main, the field 'title' corresponding to the number ID that was selected.  Can I do this in the control source , and if so, what would be the line of code, because what I've tried doesnt work.

Thanks
0
Comment
Question by:salibes
  • 7
  • 7
15 Comments
 
LVL 7

Expert Comment

by:ildc
Comment Utility
Easiest but from a performance point of view far from a good solution :

make sure your form is based on table main. Create you combobox with the wizard and select in the first screen the third option. This lets the combobox look up values on your form.
You textbox only need to be bound to your title field to see the result.

If this isn't quiet what your looking for, there is also a solution that involves some coding and recordsets.

greetings
0
 

Author Comment

by:salibes
Comment Utility
Thanks -

I had thought of that, but I can't base my form on one table.  This forms purpose is to add a record to another table, say baseTable.  And it's likely that it would be selecting a id number from main, which should auto fill-in some textboxs, and select another id from a dropdown in a third or fourth table, and update similarly.  at the end, when add record is pressed these will be updated to table, baseTable.  I'm trying to do this as to avoid update anomalies. does this make sense?

Thanks
0
 
LVL 7

Accepted Solution

by:
ildc earned 100 total points
Comment Utility
Then I guess there's only one other possibility... Code it.

It could look something like this :
on error goto ErrProc

dim rstLooking as dao.recorset
dim dbThisDb as dao.database
dim strSql as string

dim strsql = "SELECT TITLE FROM MAIN " _
& "WHERE number = " & ME.COMBOBOX


Set dbthisdb = dbengine.workspaces(0).databases(0)
set rstlooking = dbthisdb.openrecordset(strsql,dbopenforwardonly)

if not rstlooking.eof then
me.TEXTBOX= rstlooking(0)
endif

ExitProc:
exit sub
ErrProc:
msgbox err.number & " " & err.description
resume exitproc


In this example COMBOBOX = the name of your combobox, TEXTBOX is your textbox to fill.

Is it a bit clear ?

0
 

Author Comment

by:salibes
Comment Utility
I think I'm almost there, I understand your logic, but have a little trouble with the syntax.  When I ran the code, after modifying my form names, I get a compile error: user defined type not defined error, for the lines with doa.database.  when i comment them out i get '3001' invalid argument for the line: Set rstLooking = dbThisDb.openrecordset(strsql, dbopenforwardonly).

Did I forget to change something? The id number is unique so there will only be one match (first match is only match) does this help?

Thanks
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
are you doing this with Access 2000?  If yes, then you may need to add a REFERENCE (Tools/References) to the Microsoft DAO 3.6 Library.  Also, make sure thast you copied the code that ildc showed you, correctly.  The line that was posted read

dim dbThisDb as dao.database


but in your response, you said the error was on the line

doa.database  

notice that you SHOW "doa.database" which SHOULD be "DAO.Database"

Arthur Wood
0
 
LVL 7

Expert Comment

by:ildc
Comment Utility
little correction

dim rstLooking as dao.recorDset instead of
dim rstLooking as dao.recorset
0
 

Author Comment

by:salibes
Comment Utility
i increased it to 100 since youve been helping me for long.

Dim rstLooking As DAO.Recordset is what I have... gives me same error, user defined type not defined. I see no references under tools.  

anything you can think of?

thanks
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 7

Expert Comment

by:ildc
Comment Utility
What Access are you using ?
Normally in the CODING WINDOW, you should see in tools References. By clicking on it you can then select the appropriate library.
0
 
LVL 7

Expert Comment

by:ildc
Comment Utility
Try as recordset instead of dao.recordset and as database instead of dao.database. Maybe this helps
0
 

Author Comment

by:salibes
Comment Utility
Access2000
the references is grayed out  , i can't click on it.  

in the microsoft help it has the same syntax as yours to declare a dao recordset...
0
 

Author Comment

by:salibes
Comment Utility
i tried to change it to:

Set rst = Me.Recordset
rst.FindFirst "WBS_Map.completeNumber  = '" & Me.Combo33.Text & "'"

it says it doesn't recognize WBS_Map.completeNumber as a field.

WBS_map is the table refered to as main, the query works with fields in basetable (form uses as source), is there anyway to access fields from other tables?
0
 
LVL 7

Expert Comment

by:ildc
Comment Utility
you should surely use me.recordsetclone. This makes a copy of the recordset of the form. But since your form is based on a combination of other tables, I ddon't think this will do the trick. Meanwhile I'm looking around to find the reason why your "references" are gryed out. It is possible you'll need to reinstall Access.
0
 

Author Comment

by:salibes
Comment Utility
I re-installed, got the references to work, and checked doa.  the code now works, thanks alot for sticking with it.  I gave you the points but if you have a minute i was wondering if you could explain this code, so i understand what its doing and don't have this trouble again:

Dim dbThisDb As DAO.Database
Set dbThisDb = DBEngine.workspaces(0).databases(0)
Set rstLooking = dbThisDb.openrecordset(strsql, dbopenforwardonly)

and does rstLooking(0) select the first record in recordset.

Again, Thanks.
0
 

Author Comment

by:salibes
Comment Utility
Very helpful
0
 
LVL 7

Expert Comment

by:ildc
Comment Utility
Thanks, now I don't have to look anymore why your references didn't work anymore.

Now, explanation.

To be able to use a recordset you first have to declare all the right variables.
Since a recordset is a 'member' of a database that is on its turn a member of the workspace (this is your database container) that is on its turn a member of the dbengine in the object model of access, you need to SET the object to the right instance. Sometimes people use the currentdb() as instance, but this is dangerous because this creates a copy of the current, active database. The line you see there with the (0)'s refers to the first active database. (Maybe you know already that you can only open on database per access session manually, but through code it is possible to have several databases open at the same time in the same access session, each referred to as databases(#).
Now once you set the reference to the right database, you need to open (create) the recordset. Typically you can use a lot of things as source for the recordset (first parameter). It could be the name of a table, query or an sqlstring. Next you need to specify the type of the recordset. Shortly explained, dbopentable works only if the source is a table, dbopendynaset is used for recordsets you want to update, dbopenforwardonly is used if you want to cycle through the recordset in one direction, dbopensnapshot creates a non-updateble image of the recordset.

Then de rstlooking(0):
By default when you created the recordset, it is not populated. Meaning that the only record that is 'in' the recordset is the first one. To populate the recordset you need to do a rstlooking.movelast (to move the pointer to the last record of the set) and if you want to perform actions on the records you'll need to do a ..movefirst. Now since our recordset only has one possile record as a result, I do not need to do this.
To be honest, as a trainer I should have given the good example by using the correct syntax, and not use te default member of the recordset. The correct syntax is
rstlooking.fields(0).value . This means that you have there the value for the first field of the recordset (and in your case you only have one field in your sqlstring)

I hope it is a bit clear now.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Send email from MS Access macro 7 31
Append - add current date 5 25
Valid email addresses 6 20
Dcount unique 6 21
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now