Solved

textbox control source

Posted on 2002-06-20
15
257 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
ID: 7096170
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
ID: 7096217
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
ID: 7096306
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:salibes
ID: 7096426
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
ID: 7096504
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
ID: 7096554
little correction

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

Author Comment

by:salibes
ID: 7096610
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
 
LVL 7

Expert Comment

by:ildc
ID: 7096627
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
ID: 7096644
Try as recordset instead of dao.recordset and as database instead of dao.database. Maybe this helps
0
 

Author Comment

by:salibes
ID: 7096653
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
ID: 7096769
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
ID: 7096863
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
ID: 7096915
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
ID: 7096940
Very helpful
0
 
LVL 7

Expert Comment

by:ildc
ID: 7097028
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

861 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