[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Using MS ACCESS 2000 - I'm trying to build a form based on tables that I created. I have 3 fields, and basically what I want is to enter the cpt code field, and have the other two fields automaticall

Hi,

Using MS ACCESS 2000 - I'm trying to build a form based on tables that I created.  I have 3 fields, and basically what I want is to enter the cpt code field, and have the other two fields automatically populate based on the data in those tables (CPT Fee, and CPT Desc)

I read through other questions and answers, but I couldn't find exactly what I'm looking for.  A lot of what I found on your site was about combo boxes.  

My form is just three text fields.  I made the control source match the tables.

Here is a sample of my tables:

Table 1 -   CPT Codes
Field name: CPT Code


Table 2 - CPT Desc
Field names:    CPT Code
            Desc      

Table 3 - FEE
Field names:    CPT Codes
            Fee

I created a relationship from Table 1 CPT code to the CPT code on the other two tables.

What do I need to do to get my form to work the way I want it to.  I want to enter only the CPT Code, and then have the other two fields (desc and fee) automatically populate with the data I have loaded in my table(s).

Anyhelp you can provide would be much appreciated.  Thanks!


0
moorecm
Asked:
moorecm
  • 2
1 Solution
 
med39Commented:
It's not clear to me why you have three different tables.  It looks like you could have one table with three fields.  [CPT Code], [CPT Descr], [Fee]
0
 
moorecmAuthor Commented:
I originally had all three fields in one table, but when I couldn't get the form to do what I needed it to, I split the tables up.  I can put them back to all 3 fields in on table, but I'll still need assistance on how to get it to do what I need it to.

Thanks very much!
0
 
med39Commented:
Dear MooreCM,

Put the three fields in one table.

On your form, put a combo box in the header or footer.  In the "AfterUpdate" event of the combo box, put a procedure that follows this format:

Private Sub cboSearch_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[CPT Code] = " & Str(Nz(Me![cboSearch], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

There's a wizard that you can use to create this code.  The wizard appears when you drop the combo box from the "toolbox" onto the form.  When the wizard opens, select the radio button labeled "Find a Record on my form based on the value I selected in my combo box".  Then follow the wizard's advice.

-med39
0
 
jjafferrCommented:
On "After Update" [Event Procedure] of [CPT Code] field have this code:

'if [CPT Code]  is TEXT, then use this code
[CPT Fee]=dlookup("[CPT Fee]","FEE","[CPT Code]= '" & [CPT Code] & "'")
[CPT Desc]=dlookup("[CPT Desc]","CPT Desc","[CPT Code]= '" & [CPT Code] & "'")

'if [CPT Code]  is NUMBER, then use this code
[CPT Fee]=dlookup("[CPT Fee]","FEE","[CPT Code]= " & [CPT Code] )
[CPT Desc]=dlookup("[CPT Desc]","CPT Desc","[CPT Code]= " & [CPT Code] )


jaffer
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now