Setting/Displaying records in complicated form

[Monterey_Manzer]

Hi All,

So I've got a pretty large form that is pulling together records from many different tables. It's designed to replicate the paperwork my coworkers will be reading to enter the data (kind of an annual audit). A fair amount of this data is simply being presented on the form so we can verify our information is still correct. In some cases we have never gotten around to entering this information so a few of these fields will be entered once and then, assuming no changes, verified. However, I'm running into a problem that I've tried several workarounds and I'm hitting a wall on and I hope someone can offer some suggestions. For background, this audit is on customers who are college students and each student will have as many audit records as they have years in college.

Here's the issue:

One of the fields to be filled out is the number of units required for full time status (College course units, full time student status). This value is associated with specific colleges so it is found on the Schools table. However, there are three fields one for each degree type we deal with (BS, MS, PhD). This means there are three fields associated with this unit requirement, but only one ever applies to each individual record (based on the school attended and degree level pursued). I have figured out how to use an empty text box and then add that value to the correct record in the Schools table based on School attended and degree pursued using the following code:

Dim strSQL As String, ReqUnits As Integer, Degree As String

    Select Case Me.Degree.Value
        Case "BS"
            Degree = "[Unit Requirement: BS]"
        Case "MS"
            Degree = "[Unit Requirement: MS]"
        Case "PhD"
            Degree = "[Unit Requirement: PhD]"
    End Select

    ReqUnits = Me.txtReqUnits.Value
    Debug.Print ReqUnits
    strSQL = "UPDATE Schools SET " & Degree & "='" & ReqUnits & "' " & _
             "WHERE School='" & Me.School.Value & "';"
    
    DoCmd.RunSQL strSQL

However, my problem is that I want to also have an accompanying box that displays this once there is a value in that field. I've tried using a combo box and the code that I will add here in just a little bit. My problem there is the combo box appears blank unless I click on it and when the drop down menu appears, there is the correct value. Is there a cleaner way to present this data?



Code for combo box row source:

Dim strSQL As String, strDEGREE As String


    Select Case Me.Degree.Value
        Case "BS"
            strDEGREE = " Schools.[Unit Requirement: BS]"
        Case "MS"
            strDEGREE = " Schools.[Unit Requirement: MS]"
        Case "PhD"
            strDEGREE = " Schools.[Unit Requirement: PhD]"
    End Select
    
    strSQL = "SELECT PartInfo.[Smart Id]," & strDEGREE & _
    " FROM ARPT INNER JOIN (Schools INNER JOIN PartInfo ON Schools.School = PartInfo.School) ON ARPT.SMARTID = PartInfo.[Smart Id];"
    Debug.Print strSQL
    
    Me.cboReqUnits.RowSource = strSQL

[June7]

How is this code executed - in what event?

If each record can have only one degree type then really should be one field or maybe two fields: Degree, ReqUnits.

Instead of combobox use expression in textbox to display value from one of the 3 fields: = Nz(BS, Nz(MS, PHD))

[Monterey_Manzer]

The first set of code is executed in the After Update event for the ReqUnits field. The second set is executed On Current of the form

The Degree and ReqUnits are fields from different tables. I will try to show how these tables are constructed:

Students(PartInfo)-Table	Schools-Table	Annual Audits(ARPT)-Table
Smart Id	School Name	Record#
Student Name	Term Schedule	SMARTID
Degree Pursued	ReqUnits: BS	Date Received
School Attended	ReqUnits: MS	Number Credits Planned
Major	ReqUnits: PhD	Research Synopsis

There are quite a few more fields than this but you get the idea. The Smart Id ties records in PartInfo to ARPT and the School name ties records in PartInfo to Schools. So the degree type is actually independent of the ReqUnits. Like I said, this is a pretty complicated form (the paper version) which necessitates a fairly complicated Access construction. Heck, I haven't even gotten to the child tables and sub-reports yet.

June7 - The textbox expression would work if I could make it reference the Schools table and include a WHERE statement that let me specify which school in the Schools table to look for the data.

I hope this clarifies things.

[June7]

I am not following data relationships and what is coming from/to where. If you want to provide db for analysis, follow instructions at bottom of my post.

[Monterey_Manzer]

Okay, I made a simplified mock up db that only includes the forms/tables we are concerning ourselves with. The sensitive records have been removed but the data relationships are all the same. Take a look and I will be happy to explain anything that doesn't make sense.MockUp.zip

[June7]

That helps some. Forget suggestions in post 2.

Didn't provide sample student records so I am not testing this, but try a DLookup in textbox expression or in a query where Students table is involved so its fields are available for referencing:

DLookup("[UnitRequirement: " & [Degree Pursued] & "]", "Schools", "[ID Number]=" & [School Attended])

I am assuming the school ID is saved in Students table. However, the text School field set as primary key. If saving the ID Number then it should be the primary key. If saving the text value then the DLookup criteria will be:

"School='" & [School Attended] & "'"



Advise no spaces or special characters/punctuation (underscore is exception) in names. If used, must enclose in [].

[Monterey_Manzer]

Thanks June!

I will give what you suggested and shot and update this post. With respect to the naming conventions, I was learning as I constructed this dbase and so I can clearly see the development of my understanding and preferences as I go through field names and code writing. At some point I will go back through all my older forms and VBA code to apply the lessons I have learned ( will probably noticeably speed up performance) but I haven't had the spare time yet.

I will provide new and interesting error messages or mark this thread as solved once I've applied your suggestion.

[Monterey_Manzer]

I tried using the DLookup function straight within the text box control source. That didn't work out so well so I went into the VBA code and used DLookup in there. This is an addition to the earlier code in the On Current event because I already had some of the values I needed to make it work. So far it's working great!

ReqUnits = DLookup("" & strDEGREE & "", "Schools", "[School] = '" & Me.School.Value & "'")
    
    Me.txtReqUnits.Value = ReqUnits

Thanks for all the help June7!