×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

Auto Fill Fields

Auto Fill Fields

Auto Fill Fields

(OP)
Novice user:
I would like to fill a field within a form based on another field.  I have a seperate table with both fields "matched" in them.
Do I need to create a macro or query to auto fill the "empty" field?
The table does have one field in it now....I just want to auto fill the other...
Please remember I'm a novice and am unfailiar with "code"

Thank you!

RE: Auto Fill Fields

Hi,
If I understand this correclty you have tables set out as follows (for example): Table1 stores Surname and Firstname Table2 stores Surname and Address so the two are "matched" on surname.
Now you want your users to enter Firstname on the form and the other field to bring up address, is that correct?
If so then you can use the following:

Private Sub Text1_AfterUpdate()
  Dim dbs As Database, rst As Recordset
  Dim strSQL As String, LastName as String

  Set dbs = CurrentDb
  strSQL = "SELECT Surname FROM Table1 WHERE [Firstname]= '" Me.Text1.value "'"
  Set rst = dbs.OpenRecordset(strSQL)
  With rst
    LastName = !Surname
    .Close
  End With

  strSQL = "SELECT Address FROM Table2 WHERE [Surname]= '" LastName "'"
  Set rst = dbs.OpenRecordset(strSQL)
  With rst
    Me.Text2.value = !Address
    .Close
  End With

  Set dbs = Nothing
End Sub


This code works out what the "matched" value is and uses this to get the appropriate entry for the other field on the form. To make use of this code you will need to right click on the field you want the user too fill in and choose properties, then click on the event tab and for the afterupdate event choose [event procedure] then click on the button with ... on at the end of the box. This will take you to the code module with the first line and last line (the sub and end sub lines) already filled in. All you need to do is copy the remaining lines form above into the space between these two lines.
You will also need to change the table names to those you have used as well as changing the field names I've used (I.e. surname, firstname and address) to those you want. Finally you need to change the control names (I.e. Text1 and Text2) to the names of the controls on your form.

If I've misunderstood then can you give more details and I'll try again. If not then this should work fine, but let me know if you need anything clarifying,
Richard

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