Populating field based on previous field criteria

[RMPUTNAM]

I have a new patient table (PatTbl) that lists Acct, Last, First, among other fields. I would like to also have a table (RefTbl) listing referring information.

My goal is to enter the Acct number in the RefTbl and have the name fields populate by pulling from the PatTbl based on the Acct number.

I thought this would be simple, but all the manuals and tutorials I've read thru don't seem to address this issue (or at least not in a format I understand!). I've LOOKED at SQL and feel like I could get a handle on it, but I am in infant stage there.

Any help or advice will be appreciated.

Ramona

 

[Studentos]

populate <RefTbl> by pulling from the PatTbl based on the Acct number

Describe exactly how you want to append records to RefTbl. What fields or what calculations based on PatTbl should be insertet to RefTbl?

If your manipulations with PatTbl can be presented as a select query then you can create that query and then qreate insert query in Access designer (read access help on that)

 

[RMPUTNAM]

First entry field in RefTbl will be ACCT.

On entering account number, I would like to have the Last and First name pulled from the PatTbl and entered into either the next field (If I could combine them and seperate by comma) or into the next TWO fields. Either way, these are the name fields. I would also like the date of admission to pull from the PatTbl into the next field.

From there on, it's new data.

 

[Studentos]

Suppose you have form RefRbl and controls ACCT - Number, LastName

Make control ACCT AfterUpdate Event with

Private Sub ACCT_AfterUpdate()

Me.LastName = DLookUp("LastName","PatTbl","ACCT = " & Nz(Me.ACCT,0))

End Sub()

 

[Gazza2]

Studentos you have just helped me solve a problem thats been bugging me for weeks but can you go one step further if you can and tell me if you only enter part of the number can you get a subform to open displaying all matching numbers.

For instance if you are looking for 12345 and you enter 123 it will open a subform displaying the numbers from 12300 to 12399 .


Sorry to but in and thanks

 

[elbweb]

create a subform that is in the display format you want, and then apply the same basic principles that were shown to you to that subforms filter. for example:

also within access, you can use astriks to represent wildcards.

Private Sub ACCT_AfterUpdate()

subForm1.Filter = "SELECT * FROM tblTable WHERE ACCT='*" & ACCT.Value & "*';"
subForm1.FilterOn = True

End Sub()

subForm1 would be the name of the subform
subForm1.Filter = "SELECT * FROM tblTable WHERE ACCT='*" & ACCT.Value & "*';"
tblTable is the table you want the subform getting information from, ACCT is the field that you want to compare too. ACCT.Value is the text box that the account information is entered. the astriks around the acct.value allow it to return any records where the acct contains that number.

also if you want to return only the records that start with that number (i.e. for 123 you get 12345, 12346, not 24123) then remove the first astrik
subForm1.FilterOn = True turns the new filter on