Pulling data from another table (2000)

Topic

New Reply

Long ago I worked in a database called Smart. It had the greatest feature. If you typed the Product number into a field either Sales, Purchases, or Manufacturing, it would pull up the name and the cost for you from the Inventory table. As powerful as Access is, I’m sure it could be made to do this. How???
I loved this feature. I want this feature. Please help.

Reply | Quote

Replies

Access doesn’t have a directly comparable feature, AFAIK.

You can place a combo box on a form that lists all products (or product numbers); selecting one makes the form display the corresponding details. Such a combo box can be created using the Combo Box Wizard.

Reply | Quote

Thanks Hans. Unfortuneately, this doesn’t do what I had in mind. I’m so disappointed.

Reply | Quote

To simulate the behavior you describe would be a lot of work. Your form would have to be unbound (i.e. without a record source). You’d have to write VBA code to populate the form from a table, and to save the data to the table, and to let the text boxes respond to entering data – you’d have to find a way to distinguish entering/modifying data and entering a search code.

Although I understand that the feature you describe is useful, perhaps you shouldn’t spend a lot of energy in trying to make Access behave like another program, but try to use the built-in features instead.

Reply | Quote

Okay,
So — what features would take the place of this handy tool? My situation is that I have a monthly download of “Reports generated” by Employee number. I need to add Employee name and SSN to this on a monthly basis. Then keep track in one table all reports issued in the fiscal year.

Reply | Quote

You said you used that feature “long ago”, so what have you been doing recently instead?

Reply | Quote

A lot of typing. And Copying and pasting.
Actually, the need for this as only recently become urgent. Until a couple of months ago, the information was all on the mainframe. I’m trying to get the information into Access in a format that can be manipulated to create graphs, and a variety of reports.
“Long ago” I was keeping track of my husbands custom jewelry business. He hasn’t done that for a while.

Reply | Quote

Keep in mind that many database products have nice features built in, but the products are not necessarily programmable or even very flexible. Access is built to allow you to create the features you want using code and the Access interface objects. There’s no way every handy feature someone might have used elsewhere could have been built into the product and still have it fit on a hard drive and load within a week.

Reply | Quote

Hi Charlotte,
I really can’t argue with this. I do want to say that the feature I described is probably the one most useful feature I have ever found in a database. You can’t blame me for hoping there was a way to get it done in Access.
I also want to point out something you and the other experts might have forgotten. When all you have is the unaltered Access database and it’s help function, and you don’t know VBA, there are a lot of wonderful things that Access will do, that you as an enduser, NOT a developer, can not make it do.
I’m grateful that this board exists, and has patient, knowledgeable people to teach me.

Reply | Quote

Are you saying that it would populate fields for data entry, or just a search feature?

Reply | Quote

It would populate the fields. The end user had to first create a link, which involved only clicking on buttons and identifying the linked field. Then, depending on what fields were in your new table, you had only to type the data for the linked field and the other fields would be populated. I used it to cost out manufactured items among other things.

Reply | Quote

If you create a query that looks up data in the related table, and use that as record source of the form, you will come close to the feature you want. I have attached a very simple demo based on the Products and Categories tables from the Northwind sample database (in Access 97 format, zipped).

If you open the form, you will see data from the Products table. The category description, however, is from the Categories table. If you select another category from the combo box, you will see the description change automatically.

Reply | Quote

Thanks! I’m going to check this out right away.

Reply | Quote

Sounds like you need an unbound process to ‘pull’ data from a recordsource, and dump it into your current form. That’s not too difficult to do. Hey, did you ever read that VBA tutorial I wrote? It does get into Recordsets….at least the very basics.

Reply | Quote

Well, just judging from how it sounds that does seem to be what I need. I have read part of the tutorial you sent. Work keeps interfering with my learning experience. Recordsets, huh? I’ll take a look and see what it says. They also bought for the department the Access 2000 Developers Handbook. It lives on MY desk. I’ll check in it and see what I can find. At this point, it is a bit overwhelming. More than half the battle is knowing what to look for.
Lady Gnome

Reply | Quote

Definitely read up on that tutorial. It will give you enough basics in VBA to go on. Hey, don’t you live in the Dallas area? Getting into the complexities of a recordset can be a little difficult, and can be even more difficult to ‘type’ up, so if you decide to start using recordsets, let me know, maybe we can ‘chat’ over how to use a recordset.

Reply | Quote

Yes, I do live in Dallas, and I would love to ‘chat’. I’m going on a mini-vacation from tomorrow to Monday. Since I don’t have Access at home, everything will be on hold. May try to take the tutorial home.
I appreciate your help.

Reply | Quote

No problem. I’ve been ‘admonished’ for offering ‘offline’ help, but recordsets are something you really need a real time ‘tutorial’ on, because if you don’t get your questions answered while you are learning, you could end up seriously confusing yourself as to what you are dealing with. Have fun on your vacation. I wouldn’t take that tutorial home. Take a break, besides, it is easier to follow if you have Access to follow along with the examples.

Reply | Quote

Well, I’m back. I have read a bit in your tutorial on recordsets, and I found the section on recordsets in the developers handbook and read a bit in that. I think I am ready to begin to understand how this can help get the effect I’m after. Or maybe I should just take an aspirin and lay down.

Reply | Quote

An aspirin probably won’t hurt!

Do you have any specific questions about recordsets?

Reply | Quote

At the risk of demonstrating how completely ignorant I am, should I concentrate on the instructions for ADO or DAO? The way I’m reading the handbook, for writing directly into a module ADO would work.

Reply | Quote

Writing directly into a module? You import data into a table, while modules hold code routines. Are you talking about using a standard routine (not one behind a form or report) to open a recordset and manipulate data? If so, you can do that with either DAO or ADO, although the code and the object model is different for each. If you explain what you want to do, someone will be able to give you an assist.

Reply | Quote

Okay, first of all, sorry for the late reply. My apartment caught on fire. You are in the Dallas area, if you heard about the apartment fire in Mckinney Wednesday morning….that was me! So I am just now going through a lot of emails.

On to your question. First of all, if you are writing VB/VBA code, you are writing it in a module. A module can be a standard (or .bas) module, which is what you would normally see in the Modules section of a database. You can also have Class modules, which can also show up in the Module’s section of a database, or what you get ‘behind’ a form, which is also referred to as a form’s module, or form’s code page.

Next, DAO and ADO are two different monsters, though they have many similarities. DAO is (I believe) Data Access Objects, and ADO is ActiveX Data Objects. DAO is Jet’s native db interface. Jet is the database engine that Access uses. DAO has a very ‘rich’ environment.

Here’s what that means (because it is said a lot, but the meaning is usually glossed over). DAO and ADO are both ‘classes’, or really a set of classes. A class is a code ‘object’. A code object is just like a physical object. It has properties, and it can do things. Form’s are a class object. You can see a form, it has properties (such as height, width, back color, etc.), it has events (such as OnLoad, OnOpen, OnClick, etc.), and it has methods (Paint, Requery, etc.). You can ‘code’ your own custom objects. What does that do for you? Well, it really organizes your code in a different manner. For instance, when I first began with Access, I had an issue with using SendKeys, which kept cycling the Numlock key. (It’s a common issue). I solved this issue by using API calls and functions that I wrote, which monitored the current ‘setting’ for the Numlock, and kept it at that setting (actually I think I had it ‘forced’ on). The code did just what I wanted it to do, but to interact with it, I had to know what the functions and various arguments that were involved, were. Later on, I began getting into VB, and one of my larger projects included a remote ‘control’ feature, that let IS/IT people view and change various settings on the remote users machines. One such setting were the toggle keys (NumLock, CapsLock, and ScrollLock). Instead of having functions and subs all over, to handle checking there states, and changing their states, I wrote a Class that handled this. So the same code was put into the class, as was used in my first method, but now, when I wanted to have a portion of my code to ‘check’ the state of a toggle key, I would have something like this:

Dim kb as Keyboard
Set kb=New Keyboard
If kb.Numlock=True Then
‘Do Something
End if
Set kb=nothing

That code is a lot easier to ‘glance’ at, and determine what is happening.

So, with that in mind, DAO and ADO are similar objects. When you use the following ADO code:

Dim cnn as ADODB.Connection
Set cnn=New ADODB.Connection
With cnn
.Provider=”Microsoft.Jet.OLEDB.4.0″
.Open “C:MyDB.mdb”
End With

What is happening internally, is a connection made to that database, which involves putting or modifying an entry in the .ldb file, locks are placed within the database, etc. All of that is happening from those few lines of code. However, the ‘overall’ effect is you are connecting to, and opening a database. Thus, ADO and DAO are giving you ‘objects’ to deal with, instead of complicated processes. What a ‘rich’ environment in DAO means, is that DAO has properties, methods and events tailored specifically for Access. It has all sorts of features available for Access User Level security, and so on. DAO is an absolute must in two situations. The first is when speed is essential. Since DAO is tailored for MS Access, it is faster then ADO. Not so much that you would notice during normal interactions, but if you are doing heavy db interactions, DAO is MUCH faster. The second is if you need the richer environment, in other words, you need the ability to use Access Only capabilities.

Why does ADO not have access to these abilities? Simple, ADO is designed to interact with practically ANY database. Access, Oracle, SQL Server, etc. To allow for such a variety in data sources, ADO was built to handle generic database tasks. By this, I mean that almost every database system out there has common functions/features with every other type of database available. For instance, with any database, you have tables, and fields. A table is a group of fields, and a field has various properties such as a name, a datatype, a value, etc. You also will want to read and write data to and from a database. All common properties and methods. Thus, if you have the following code:

Dim rs as ADODB.Recordset
Set rs=New ADODB.Recordset
rs.Open strSQL, cnn, …….
msgbox rs.Fields.Count

regardless of what SQL string is put into strSQL, or what connection is being used (as cnn), you will be able to determine the number of fields in that recordset.

Now, DAO is the ‘default’ object within Access 97. In A2k (and I believe XP), ADO is the default object. DAO is still native, (faster and ‘richer’), but in Access 97, if you open a new database, and put in the following code:

Dim rs as Recordset

You will get a DAO recordset. In A2k, you will have an ADO recordset.

Most code is relatively interchangable for common interactions. (Both have AddNew, Update, Move (MoveFirst, MoveLast, MoveNext, MovePrevious), etc.) However, there are minor differences on how they are used. For example, with DAO, if you are going to ‘edit’ a record, you must use the Edit method (which locks the record for editing), with ADO, you simply change the values that you want to change, then use the Submit method (which is also used in DAO). ADO goes and locks the record when it writes the data. (Though I believe you can lock the records early with ADO too).

I hope I haven’t confused you more….let me know if you have any more questions.

Drew

Reply | Quote

Oh wow! Sorry to hear about your apartment. Hope things turn out all right.
Lots of good information in your post. Now, if I am understanding your post (and Charlottes) correctly, if I concentrate on writing code in ADO to start, I will be able to do most of the things I want to do. Then I can gradually learn the DAO for the “richer” things it can do, and how they are different. Thump me if I’m wrong.
Now for my immediate project, which I guess is as good a place to begin learning as any. Here are the steps I need to accomplish.

Import the monthly table of employees (by person number) for whom reports were issued.
Identify employees that are not on the employee list (Non matching query?)
Add them to the employee list.
Create a – table? – recordset? Something that includes the names and SSN along with the person number for the monthly reports. (I was thinking of using a query for this. I do have two tables to work with.)
Append the current list to the overall year to date list of employee reports.

The only part of this that I just don’t see as amenable to automation is the addition of the SSN for new employees. I’m just going to have to research and type that.
Otherwise, (rolling up sleeves) let’s see how much I can get Access to do for me.

Reply | Quote

Actually, everything you have listed should be workable with just a few querries!

Reply | Quote

[indent]

---

then use the Submit method (which is also used in DAO

---

[/indent] The method I’ve always used is Update in both DAO and ADO, Drew. And while some methods have the same names in both models, others don’t. For instance, ADO uses Find, while DAO uses FirstFirst, FindNext, etc.

Reply | Quote

Ooops, you’re right, Update, not submit. My bad. I was at work around 11pm, after moving apartments from a drenched and smoky ruins to my folks house (the cleaning depot! ) then to our new place. So I was just a wee bit tired last night.

Pretty tired tonight too.

DAO and ADO do have differences, but they are really minor in a manner of speaking. I personally NEVER use the Find features. I prefer to search strictly with SQL, so the only difference I personally see when switching between the two is the Edit command.

Reply | Quote