Relating 2 fields in VB (utilizing ACCESS table) 1

TheWave · Feb 7, 2000

I need help in associating 2 fields in a VB program. For instance, if I select a course number (from a drop-down box), I would like the program to automatically populate the course name field. These 2 fields are stored in the same table. I am having difficulty finding any information about doing this because I don't even know what to call it!

DougP · Feb 7, 2000

Well you have to "Move" to the record you want to pull. 
 
this is done by using a "bookmark" 
To move to another record you can use SEEK, if you have a Primary key in your table. 
If not you can use FINDFRIST to find the record then you get its' book mark and position to that record. 
 
syntax as shown: 
Sub SeekX() 
 
Dim dbsNorthwind As Database 
Dim rstProducts As Recordset 
Dim intFirst As Integer 
Dim intLast As Integer 
Dim strMessage As String 
Dim strSeek As String 
Dim varBookmark As Variant 
 
Set dbsNorthwind = OpenDatabase("Northwind.mdb&quot

' You must open a table-type Recordset to use an index, 
' and hence the Seek method. 
Set rstProducts = _ 
dbsNorthwind.OpenRecordset("Products", dbOpenTable) 
 
With rstProducts 
' Set the index. 
.Index = "PrimaryKey" 
 
' Get the lowest and highest product IDs. 
.MoveLast 
intLast = !ProductID 
.MoveFirst 
intFirst = !ProductID 
 
Do While True 
' Display current record information and ask user 
' for ID number. 
strMessage = "Product ID: " & !ProductID & vbCr & _ 
"Name: " & !ProductName & vbCr & vbCr & _ 
"Enter a product ID between " & intFirst & _ 
" and " & intLast & "." 
strSeek = InputBox(strMessage) 
 
If strSeek = "" Then Exit Do 
 
' Store current bookmark in case the Seek fails. 
varBookmark = .Bookmark 
 
.Seek "=", Val(strSeek) 
 
' Return to the current record if the Seek fails. 
If .NoMatch Then 
MsgBox "ID not found!" 
.Bookmark = varBookmark 
End If 
Loop 
 
.Close 
End With 
 
dbsNorthwind.Close 
 
End Sub

Snaggs · Feb 7, 2000

Just a side note about using Seek. If you're using ADO to do this instead of connecting to the JET Engine directly as in the above post, it can be extremely slow. I always prefer to use an SQL statement with a WHERE clause to obtain the record. Something more like: 
 
SELECT CourseNumber, Name FROM tblCourse WHERE CourseNumber=6 
 
This is just an example, and of course the CourseNumber is redundant in the select statement and I assumed the course number was a numeric data type. 
 
We had a project that we wrote using SEEK and it was extremely slow. When using SQL like above, we got a speed increase of 7 to 8 times. The only reason we knew it was slow is it was in a loop and had to execute it about 1,200 times. 
 
Steve 
<A HREF="mailto:sdmeier@jcn1.com">sdmeier@jcn1.com</A>

DougP · Feb 7, 2000

The only Problem with SQL (If you could call it a problem)is that it returns a group of records that match a criteria. Say you find all of the "course" as in the original post. 
Say there are 25 of them, then you will see only those 25 records. 
Now to see the rest of the 100 records altogether you have to create a button(or something to click on) which restores the original recordset. 
Like so 
SQL = "Select * From MyTable;" 
Now Of course this is what I prefer to do. And you are right is it the fastest way to work with Access 
but in the original Post 
TheWave mentioned "I select a course number (from a drop-down box), " 
TheWave wanted, I think to click on the drop down and then jump to that record???? 
 
It all depends on what you want to do.

Snaggs · Feb 7, 2000

Typically what I would do in a situation with a Combobox is read in a distinct list of courses from the table. Assuming the table is setup with a PrimaryKey that has an AutoNumber field and a Course Number other than the PK. I would read in the courses from the table and put their CourseNumber in the Combobox. Also in the combox, there is another list called ItemData. This is where the Long, CourseID would go. Now, in the cboCourse_Click event, I would use a recordset to do the following: 
 
"SELECT CourseName FROM tblCourse WHERE CourseID=cboCourse.ItemData(cboCource.ListIndex) 
 
Then I would put the information in the text box on the form with: 
 
txtCourseName.Text = rsCourse!CourseName 
 
For doing single lookups in a small table I don't think you'll find a noticeable speed difference in or the other. I was basing my assumption on he only wanted to populate Course Name field based on the course number. TheWave said: "if I select a course number (from a drop-down box), I would like the program to automatically populate the course name field" 
 
Weather he keeps and recordset open and seeks, or opens a recordset with a where clause is up to him. I was merely stating that .Seek used repetitively may not be as efficient as a where clause if placed in a loop. I'll try to stick to the topic at hand... Nice explanation of code on your behalf, I thought! 
 
TheWave, another thing you might consider is instead of using a lookup or seek for this, is to read the CourseNumber and the Course from a recordset and then do a little data tweaking/combining before you put it in the combo box. If you combine the two pieces of information, then you don't need to lookup the Course Name. Like this: 
 
cboCourse.AddItem rsCourse!Course & " - " & rsCourse!CourseName 
 
This would produce entries like: 
 
IM101 - Introduction to Math 
AM201 - Advanced Math 
CP101 - Beginning Computer Programming 
 
Then you could choose to sort your list of courses either by the Course Number or by the Course Name. You may have to do some text manipulation if your course numbers are not all the same number of characters. If you're using a true-type font for your combo box, things can get a little tricky with alignment. 
 
Cheers, 
 
Steve 
<A HREF="mailto:sdmeier@jcn1.com">sdmeier@jcn1.com</A>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Relating 2 fields in VB (utilizing ACCESS table) 1

TheWave

Programmer

DougP

MIS

Snaggs

Programmer

DougP

MIS

Snaggs

Programmer

Similar threads

Part and Inventory Search

Sponsor