Button to fill form fields based on info in another table

[formulaic]

Working in Access 2007. This involves a few topics, but the end result is in a form, so I figure this is a decent place to ask.

I want to be able to use a button on a form to autopopulate fields in the same form based on the input in another field

Say I have these 2 Tables and Columns for Explanation Purposes, they are fictional and wordy, just attempting to demonstrate what I'm after:


Account Table

• Account Number
• Account Owner
• Account Balance

Renewal Application Table

• Account Number
• Date of Application
• Reason for Application
• Account Owner at Time of Application
• Account Balance at Time of Application

The "Account Table" table is linked from another database, and is read only (if this would matter)

I want a form to write the "Renewal Application Table" where the user will input the Account Number, click a button, and the "Account Owner at Time of Application" and "Account Balance at Time of Application" are automatically filled in based on the corresponding information in the linked "Account Table" table, and are not able to be updated again (IE, this needs to be timestamped information, I do not want a user to be able to hit the update button 2 months later when the balance or owner in "Account Table" may have changed.)

Thank you for any assistance, If I need to clarify anything I will to the best of my ability.

[Robeen]

I think you have two questions here:

1. How to populate the fields in your Form with data from the 'Account Table',
2. How to make sure that once Account Owner and Account Balance have been written into your Renewal Application Table the first time, it will not be possible to overwrite those values again.

Is that correct?

[formulaic]

Yes, Robeen, those are probably the two primary points of my problem with #1 being the more critical, at least for now.

Question 2 I actually thought about as I was trying to put into words question 1, so I figured I would include it as well since it is ultimately going to be needed.

[Robeen]

Hi formulaic,

This is pretty basic, stripped down code - but I hope it helps.
What this is doing is getting the AccountOwner and AccountBalance from your Account Table - based on the Account Number that is entered on your Form.

This code should be in the Command Button.

Your Form should have a Text Box Named AccountNumber [no space].

I used the MsgBoxes to show me what I was getting - you can comment them out.

 
Private Sub Command9_Click()
Dim strSQL
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim AccountHolder, strSQL As String
Dim AccountBalance As Double
 
Set db = CurrentDb
strSQL = "Select * From [Account Table] Where [Account Number] = " & Me.[AccountNumber]
 
MsgBox strSQL
 
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
 
With rs
 
AccountHolder = rs![Account Owner]
AccountBalance = rs![Account Balance]
 
MsgBox "AccountHolder: " & AccountHolder
MsgBox "AccountBalance: " & AccountBalance
 
End With
 
End Sub

Once you are satisfied that this is working the way you expect, you can add this right after 'End With' to put the AccountHolder and AccountBalance into the fields in your Form - like this:

{You will need Text Boxes named 'Account Owner at Time of Application' and 'Account Balance at Time of Application'.}

 
Me.[Account Owner at Time of Application] = AccountHolder
Me.[Account Balance at Time of Application] = AccountBalance

Let me know if this helps!

[formulaic]

Thanks for the reply, I was hoping I wouldn't have to take this to coding as I have very little VBA experience (and most of what I do have is in excel). Having very little Access experience doesn't help either hah.

I'll work with that and see if I can come up with anything though.

Thanks again.

[Robeen]

Just put the code that I provided into the Code for your Button on the Form.
Once you start using VBA, you will start enjoying it.

Select your Command Button.
Click Property Sheet on top.
Click Event.
Click the ellipsis [...] to the Right of 'On Click'.
Choose 'Code Builder'.
When you're in the VB Editor - just paste my code in & Save.
Run The Form.

There isn't anything else you need to do till you want to start messing with it yourself.

Let me know if you need help.