Displaying Value in Unbound Control

[bullfrog83]

So you have some background to what I'm doing, I'm building a degree audit for my school which requires the building of degree requirements by matriculation year/term (stored in DegReq) and assigning them to students (StdDegReq). This is a little long but I wanted to provide as much information that I thought necessary.

There are 3 tables related to my problem:

MatricYT: MatricId (PK), MatricYear, MatricTerm
DegReq: DegReqId (PK), MatricId (FK) (degreqs are built by matric year/term)
StdDegReq: StdDegReqId (PK), PersonId (FK), DegReqID (FK)

I have a form whose record source is StdDegReq which allows a user to assign a degree requirement plan to a student. The form has two combo boxes: cboMatricId (unbound) and cboDegReqId (bound to DegReqId).

cboMatricId's rowsource pulls all available values in MatricYT and cboDegReqId's rowsource pulls all available values in DegReq. Because I want the values in cboDegReqId to be determined by the MatricId selected in cboMatricId I've put the following code in cboDegReqId's GotFocus event:

Me.cboDegReqId.RowSource = "SELECT DegReq.DegReqId, CodeProgram.Program & "" / "" & CodeDegree.Degree & "" / "" & CodeCurriculum.Curriculum AS PDC " _
                        & "FROM ((DegReq INNER JOIN CodeProgram ON DegReq.ProgramId = CodeProgram.ProgramId) INNER JOIN CodeDegree ON DegReq.DegreeId = CodeDegree.DegreeId) " _
                        & "INNER JOIN CodeCurriculum ON DegReq.CurriculumId = CodeCurriculum.CurriculumId WHERE DegReq.MatricId = " & Me.cboMatricId

After I select a MatricId and then a DegReqId, close the form and open it up again, cboDegReqId displays the deg req plan I assigned to the student but the cboMatricId combobox remains blank because it can't find the MatricId associated with the DegReqId. My question is, how do I get the combobox to display the matric Year/Term of the MatricId of that DegReqId? The easiest way would be to store MatricId in StdDegReq also and bind the control to that field, but that would violate normalization rules since MatricId is already stored in DegReq. I've been trying to figure this out but it has me stumped. Your help is much appreciated. Thanks!

[NeoPa]

I can tell from how your question is formulated that I'm dealing with an intelligent, competent person, so I'll start with this link Example Filtering on a Form).

If this doesn't help you to your solution, or if you find it misses the point, post back and we can progress in more detail.

[bullfrog83]

I downloaded the sample db and reviewed it but found that it doesn't help me (unless there's something that I missed).

Taking what I wrote above, here's perhaps a simpler way of explaining what I want to do:

This is the rowsource of my bound combobox cboDegReqId:

SELECT DegReq.DegReqId, DegReq.MatricId, CodeProgram.Program & "/" & CodeDegree.Degree & "/" & CodeCurriculum.Curriculum AS PDC
FROM ((DegReq INNER JOIN CodeProgram ON DegReq.ProgramId = CodeProgram.ProgramId) INNER JOIN CodeDegree ON DegReq.DegreeId = CodeDegree.DegreeId) INNER JOIN CodeCurriculum ON DegReq.CurriculumId = CodeCurriculum.CurriculumId;

The first two columns I have hidden by setting their width to 0. How do I get the MatricId of the selected DegReqId in the bound combobox to display in the unbound combobox cboMatricId? I can set the control source of cboMatricId to:

=DLookUp("MatricId","DegReq","DegReqId = [txtDegReqId]")

This works for the row that has a DegReqId, but on the next row I'm unable to select a value in cboMatricId which poses a problem if I have to assign another DegReqId to the student because he's double majoring for instance. I know that's probably because of the DLookup function and also because on the new record row there's no DegReqId because one wasn't selected yet in cboDegReqId and you can't select a value in cboDegReqId until you select a value in cboMatricId since the values in cboDegReqId are dependent upon what's selected in cboMatricId. So I'm stuck in a catch-22!

[bullfrog83]

NeoPa
Since I see you are an administrator what is the difference between "Post Reply" above the Quick Reply and the "reply" which takes you to the QUick Reply at the bottom of every post? I'm new here and am not sure when to use which one.

[NeoPa]

First let me say that this whole area is in a state of flux at the moment, so the details are not guaranteed except at time of my posting this :

• The Post Reply button takes you straight to the Advanced Reply page with no automatic quoting. The Advanced Reply page has more features available than the Quick Reply.
• The Quick Reply box doesn't involve navigating away from the page first. Submitting the reply from here can be done either directly (Post Quick Reply), or via the Advanced Reply page (Go Advanced). The latter will take anything already prepared and preview it ready for continuing in the advanced mode.
• The Reply button associated with each post will take you straight to the Quick Reply box, but will set a flag to add a quote of the associated post to your reply. You will see this if you subsequently select Go Advanced.

[bullfrog83]

@NeoPa
Thanks for the clarification! However, do you have any ideas concerning my issue? I followed your link and downloaded the sample db but it didn't help me. My follow-up is post #3 in this thread where I hope I provided some more useful information. Thanks!

[NeoPa]

Sorry for the delay in responding. There is a lot to look through in here so I had to postpone looking at it until I had a bit of time within which to think it through. Unfortunately, I find myself a little stuck. Looking at your bound ComboBox - cboDegReqID - I can follow the reference to [DegReq], as that is explained in the OP (post #1). I get a bit lost when trying to make sense of the rest of it though.

As for cboMatricID, I cannot truly say what this should be, but I'm pretty sure a DLookup() call is not required. A possible alternative, without properly understanding your situation (worth testing out though), might be :

SELECT [MatricID]
FROM   [DegReq]
WHERE  [DegReqID]=Forms!{FormName}.txtDegReqID

Clearly, you'd need to substitute {FormName} with the actual name of the form.

[bullfrog83]

@NeoPa
No need to apologize! I'm grateful for whatever assistance I receive. I just wasn't sure if you saw it or not so I wanted to make sure and certainly didn't mean to come off as "pushy".

I may be having a brain lapse here but where do you want me to insert the SELECT statement that you provided? I already have the row source of the cboMatricId as follows:

SELECT MatricId, MatricYear & "/" & MatricTerm AS MatricYT
FROM MatricYT
ORDER BY MatricYear & "/" & MatricTerm DESC;

I certainly understand what you're saying about it being difficult to understand this without actually seeing it. I can easily have an unbound textbox that pulls the year/term of the MatricId for the selected value in cboDegReqId. But that seems superfluous, especially since there'll be a blank combobox for each record. If I find no solution I suppose I'll have to add MatricId to StdDegReq and bind cboMatricId to it. I'd hate to do it but what other choice would I have?

[NeoPa]

My suggestion was in response to your DLookup() code included in post #3. I was under the impression that you wanted it filtered to list only those items related to the DegReqID control on your form. Have you tried the suggestion? Does it find the right values? We can sort out tidying it up to provide fuller info later if we're on the right lines.

[bullfrog83]

@NeoPa
You understood correctly. My question was do you want me to insert the code you provided as the rowsource? If so, then that wouldn't work because for a new record, you select the MatricId first in the unbound combobox and then the DegReqId in the bound combobox. The rowsource of cboMatricId would find no values since a DegReqId isn't selected yet.

I could have created one big combobox that concatenates the values of MatricId & DegReqId which would solve my problem. However, it would result in hundreds of values to scroll through since you can have dozens of DegReqIds tied to one MatricId. Therefore, I thought it best to break it up into two comboboxes for easier selection: the first is unbound that allows you to select the MatricId, and the second is bound that allows you to select a DegReqId that's tied to the selected MatricId. Getting the MatricId of the selected DegReqId to display in the unbound is my problem.

[NeoPa]

In that case try :

SELECT [MatricID]
FROM   [DegReq]
WHERE  Nz(Forms!{FormName}.cboDegReqID,'') In([DegReqID],'')

** Edit **
The text is for the Record Source, as you say.
Actually, I suspect changing this would be unnecessary. Simply call a .Requery() whenever the cboDegReqID changes (AfterUpdate) to update the list to match.

[bullfrog83]

@NeoPa
As I reread our posts I think we're not on the same page. My problem does not concern the rowsource of cboMatricId. I can get that to work fine. Let me explain it this way:

1) I open the bound form
2) I select a MatricId from the unbound combobox cboMatricId which then determines the available values in my bound combobox cboDegReqId
3) I select a DegReqId
4) Record saves
5) I close the form
6) I reopen the form and the DegReqId I selected displays in cboDegReqId but no value displays in the unbound cboMatricId because it's not bound to any field. I can get the right value to display if I use a DLookup (Post #3) but then if I decide to add another DegReqId to the person, I'm unable to select a value in cboMatricId for the new record. Maybe there's no way to do what I want to do?

[NeoPa]

I suppose I'm confused by your requirement. You have a bound ComboBox (cboDegReqID) and an unbound one (MatricID). From this I understand that you want the value of cboDegReqID to be saved with the form's record, but you don't want the value of cboMatricID to be.

From what you're describing though, it seems that you're surprised that this value is not saved - is not available next time you navigate to that record. Am I missing something here?

[bullfrog83]

@NeoPa
No, you got it. However, I'm not surprised that the value isn't saved. I know it's not because the control isn't bound and the control can't be bound because the form is bound to table StdDegReq which does not contain the MatricId field (see OP). The only way I could bind cboMatricId was to add MatricId to StdDegReq but then that would violate normalization rules.

I was just wondering if there was some way to fanagle the MatricId to display anyway. Because I can get an unbound textbox to display the MatricId by using a DLookup as the textbox's control source no problem. But I can't seem to do the same for a combobox.

[NeoPa]

In that case it may help to know that what is displayed is the .Value of the ComboBox control. It's obviously a good idea if the value matches an entry in the list, so the .RecordSource should still be set to reflect the list of valid items selectable. To set the currently selected value of that ComboBox though, you need simply set the .Value in the code. Does that throw more light?

Essentially, when you need this set, have a line in your code something like :

ComboBox = DLookUp("MatricId", "DegReq", "DegReqId=[txtDegReqId]")

[bullfrog83]

@NeoPa
Where do I set this in the code? On what event?

[NeoPa]

Sorry. I was confusing your intelligent approach with more specific Access experience. I skipped some important information. Let me point you a little more clearly.

First start by identifying the control on your form that it will be filtered on. In the code you specify [txtDegReqId], yet earlier you referred to a ComboBox named [cboDegReqId]. Whichever is the one you need, you should create an AfterUpdate procedure (Select control; Click in the After Update property of the Properties Pane; Select the value "[Event Procedure]"; Enter the code).

The code should be something like :

Private Sub cboDegReqID_AfterUpdate()
    cboMatricID = DLookup(Expr:="MatricId",
                          Domain:="DegReq",
                          Criteria:="[DegReqId]=" & Me.[cboDegReqId] & ")
End Sub

[bullfrog83]

@NeoPa
That did the trick! Thanks for your help and patience!

[NeoPa]

Not a problem.

Please come back with more if/when you need.