Disappearing Combos

[Robbyp2001]

Hello folks, I wonder if anyone here can help me? I have form which contains a number of combo boxes, but there are two of particular interest. [Target Level] and [Target Grade]
Each database record has a Year Group number 7, 8, 9, 10, 11, 12 or 13 and this number determines which of the combo boxes is used.
Year Groups 7-9 will populate Combo box [Target Level] but not [Target Grade], whilst Year Groups 10-13 will populate [Target Grade] but not [Target Level].
It seems pretty straightforward but I find users are accidentally populating the wrong combo box. What I’d like to do therefore is set the combo boxes to become invisible when not required. So:
Yeargroup Combo [Target Level] visible Combo [Target Grade] visible
7 ...................................Yes ...............................................No
8 ...................................Yes ...............................................No
9 ...................................Yes ...............................................No
10 .................................No ................................................Yes
11 .................................No ................................................Yes
12 .................................No ................................................Yes
13 .................................No ................................................Yes
Is this possible? It would certainly make it very difficult to enter data into the wrong field.

Thanks folks

Rob

 

[PNGBill]

You can efect the combo box Visible Property or you could use one combo box and have specific options available depending on the age of the user.

At the point where the record is selected, you would test the age and efect the Form Control (combo box) accordingly.

Because there are so many options it may be easier for you to explain a little detail of how the form is Opened and or the Record Selected for further advice on what and where code to use.

 

[Glue]

You could use an if function based on the numeric value of the yeargroup....
In after update of the Yeargroup combobox

Write code that states If your yeargroup is less than 10 then
make targetlevel visible.

Place targetlevel combo on top of targetgrade. When targetlevel is visible the user won't be able to access the lower level targetgrade. When it is not visible target grade will be visible.

 

[Robbyp2001]

Hi Bill, thak you for your prompt response.

I have a form that contains student details, name, form class etc. with a subform (continuous form) containing a list of subjects that the particular student has opted for. Beside each subject there is a button that opens a different form, used for inputting data. The underlying query for this form is based on the information contained on the previous for, Student ID, Subject ID and Yeargroup ID. This is the form that contains the combo boxes.

I would show you the screen dumps for these, unfortunately I don't think this system allows it. I'll be happy to provide any other info required.

 

[Robbyp2001]

Hello Glue and thank you for your prompt response. This is what I had envisaged. Unfortunately I know very little about programming code.

Would it be possible to give me an example of this code?

Rob

 

[PNGBill]

At the point where StudentID is known, you are able to have your Form/s display to reflect any stored data eg StudentAge, StudentSex etc.

If you can attach a stripped down copy database with no sensitive data I, or others, should be able to assist.

 

[Robbyp2001]

Bill, how do I attach the dbase?

 

[PNGBill]

When Posting you should have Additional Options below where you type. This includes Manage Attachments.

Try and get your database size below 1mb.

 

[Robbyp2001]

I see it Bill. I will try to downsize the DB a bit more.

 

[Robbyp2001]

Hey Bill, I've deleted everything that I can but when I try to upload I get the following message:

"Your submission could not be processed because a security token was missing."

The file is 18,212 kb. Is that still too big?

 

[PNGBill]

Never heard of Security Token. 18mb a bit big

Try and compress the file.

Also, did you Compact it first ?

Trust it is not a split database.

 

[Robbyp2001]

I'll see what else I can delete in terms of forms and reports. There are many of them.

I did compact the db first and It is not split.

Rob

 

[Robbyp2001]

Hi Bill

I took out everything that I could and zipped what was left. I hope that this works.

From the main switchboard:
Select View/Edit Student Reports
Select Mid and End Term Written Reports....
Select View/Edit by Form Class
Select AAK Year 9
Select Mid Term (any subject)

This is the data input screen. The two combo boxes are located at the bottom of the screen/: Target Grade and Target Level.

When the Year Group ID (I've left it temporarily visible)= 7 or 8 or 9 then Target Grade should be invisible.

When the Year Group ID = 10 or 11 or 12 then Target Level should be invisible.

I hope the files comes through.

Thanks again Bill

Rob

 

[PNGBill]

Everything appears OK. I will study your issue during the day

 

[Robbyp2001]

Thanks Bill

 

[PNGBill]

Paste this code in your On Load event of Mid Term Report Screen by Form Class

Dim YearGrade As Integer
    YearGrade = Me.Year_Group_ID
 
    If YearGrade < 10 Then
        Me.Combo92.Visible = True
        Me.Label65.Visible = True
    ElseIf YearGrade > 9 Then
        Me.Combo96.Visible = True
        Me.Label70.Visible = True
    End If

Have spaces in Names is not recommended by many on this Forum.

Trust the code works and makes sense

ps forgot to mention... set the above four controls to be invisible by default.

 

[Robbyp2001]

This works brilliantly Bill. Many thanks....you're a star!

I agree with your observations re naming fields.

Rob

 

[Robbyp2001]

Bill, can I presume to ask a further question?

I have a report which is based on the information submitted in the original form.
In the report are two text boxes: MTR Target Level and MTR Target Grade. Would a similar code inserted into the On Load event do the same as the code you wrote for the form?

So I have
Label86 and textbox MTR Target Grade
Label87 and textbox MTR Target Level

Could you show me the syntax to use for this?

Thanks once again


Rob

 

[PNGBill]

I am sure this can be done but not as simply - I understand.

One sure way is to have two Reports. One for each age group.
Similar code as used will assign the Report Name to be used. ie If ... Then etc

This would be included in the code behind the OnClick event where the report is called.

Does this assist ?

 

[Robbyp2001]

Thanks Bill. I understand where you're coming from with this. I'll have a look, however to create a different report for each yeargroup would effectively mean 12 reports as there is one each for Mid Term and End Term. Maybe too much.

Rob