Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Relating 2 fields in VB (utilizing ACCESS table)

Relating 2 fields in VB (utilizing ACCESS table)

Relating 2 fields in VB (utilizing ACCESS table)

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!

RE: Relating 2 fields in VB (utilizing ACCESS table)

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")
' 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.
intLast = !ProductID
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

End With


End Sub

RE: Relating 2 fields in VB (utilizing ACCESS table)

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.


RE: Relating 2 fields in VB (utilizing ACCESS table)

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.

RE: Relating 2 fields in VB (utilizing ACCESS table)

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.



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close