combobox differant with prior input

[jarheadjim]

I have a form with many fields, 3 of them are [male / female], [height], [maxweight]. The height/weight standards are differant for men and women (for the military), and I have them saved on 2 seperate tbls. Here's where it gets tricky (to me). I have a combobox on the form that lets you select the height of the person, and I want to make it look into the appropriate table for the corresponding maxweight. How do I get it to check the response in the [male / female] field and then use the appropriate table? I wish I was better at explaining these things. Thanks in advance for any help you can throw my way.

 

[jarheadjim]

and one other thing

after the fields are filled appropriatly...I would like it to change the background color of [weight] if [weight]>[maxweight], but I'm not sure what statement to use. I'm so lost

 

[Dave Eyley]

If I understand you right, you have a database with containg a number of people and their weights and heights?

You also have tables containing ideal weights for heights for types of people (i.e. Male or Female) etc...

When you input your people details you should record the type of person (male/female) so that when you enter their details into the form the field's AFTERUPDATE property can be coded -

If Type = Female then DLOOKUP("[IdealWeight]","[FemaleWeight]","[Height]=Forms!ThisForm![Height]")

This would return the ideal weight to the form control called 'IdealWeight'.

In the same AfterUpdate property, it should be possible to then do -

If Idealweight < Weight Then Me!Weight.Backcolor=255

This should turn the person's weight field red.

It's one way - I'm sure you'll get more.

Best of Luck

Dave E

 

[jarheadjim]

halfway there

The backcolor coding works great, except it affects every record instead of just the one currently being worked on. How do i fix that? Now I'll try out the lookup on the male/female part.

 

[jarheadjim]

no joy

They male/female part is not working for me. This is how I'm going about getting the [maxweight] right now. For [height] under afterupdate the code is "[MaxWeight] = [Height].Column(2)" , it's rowsource=[tbl male ht/wt max]. How do I go about making it select from either the above or [tbl female ht/wt max] as defined in [male / female].

does that make more sense?

 

[Dave Eyley]

The two tables you mention have the same field types?
I would let the fieldnames in each table be identical.

Have a look at using the recordset property.
Does the form prompt for male/female as well as weight?

If so use the If [Gender]=Male Then condition.


A simple example -

Dim MyDb As Database.MySet As Recordset
Set MyDb=DBEngine.Workspaces(0).Databases(0)

If Me![Gender]= "Male" Then Set MySet = MyDb.OpenRecordset_("SELECT DISTINCTROW " & " MaleTable.PrimaryKey " & "FROM_ MaleTable;")
End If

If Me![Gender]= "Female" Then Set MySet =MyDb.OpenRecordset_("SELECT DISTINCTROW " & " MaleTable.PrimaryKey " & "FROM_ FemaleTable;")
End If

MySet.MoveFirst
DoUntil MySet.EOF
If MySet![Height]=Me![Height] Then
Me![MaxWt]=MySet![MaxWt]
Exit Sub
End If
MySet.MoveNext
Loop

MySet.Close
MyDb.Close

When you get back on the form the AFTERUPDATE can deal with the colour issue - but I warn you - if you have a continuous form or datasheet view all of the fields in the column will change color!
I found this out while experimenting while trying to highlight out-of-date records. There may be a way - but I've yet to find it.


A thought - would it not be better to have both male & female weights in one table against the height field?
One table could then feed one form and show the 3 columns for the height range required. I haven't thought this thru' but it sounds logical.

I hope this helps. There must be loads of ways of doing it.

Good Luck.

 

[bradcccs]

You will need to change your field name of "Height".

Height is a reserved word that refers to the Section Detail Height.

Even using [ ] can prove unsuccessful.

Just removing the easy problems for you.

Brad

 

[jarheadjim]

how about this

I'm sorry I haven't responded back until now, my wife was hospitalized last Monday, and I have been rather busy with my children.

I can put all the maxweight's in one table and have 3 columns;[MarHeight],[MaleMax],[FemaleMax]. Would that make it easier? How would I make it choose the correct column (2 or 3) based on the selection of [Gender]?

 

[Dave Eyley]

Hi Jim,

Sorry to hear about you wife going into hospital. Hope everything works out well.

As to the Access problem...I've been thinking about this and come up with a few suggestions. I'm sure there's loads of different ways of doing this, some better, some worse, but here goes -

In the one table there would be the following fields:

WeightMax_F
WeightMin_F
Hgt_F
WeightMax_M
WeightMin_M
Hgt_M

Self explanatory, yes?

Create a main form with 2 subforms, one for male results and one for female results. Each one of the tables would be linked to a combo on the main form. Each one having a different RECORDSET to include the gendered fields from the table. The mainform combos would be called :

HeightF
HeightM

and each subform linked by Master/Child fields HeightF/Hgt_F and HeightM/Hgt_M

When the appropriate combo is filled in, the AFTERUPDATE property could get the associated subform to display the result.

Extra features could be added like -

When one combo is filled in, say the Male one, the OnGotFocus of that combo could make the Female subform invisible and vice versa.

There must be a relationship between height and weight, so why not caculate the weights from the heights for each entry?

It's an interesting db and I hope you achieve a good result.
I would be happy to discuss it some more, if you'd like.

Regards

Dave E

 

[jarheadjim]

so far so good

Thank you for your concern, she's doing much better.

I solved the color problem by inserting this

If [Weight] > [MaxWeight] Then Me!Weight.BackColor = 65535
If [Weight] < [MaxWeight] Then Me!Weight.BackColor = -2147483643
If [Weight] > [MaxWeight] Then Me![bodyfat%].BackColor = 65535
If [Weight] < [MaxWeight] Then Me![bodyfat%].BackColor = -2147483643

in "on current" for the whole form. Now it does the comparison everytime a record is viewed which makes it only color the appropriate records.

I still don't quite grasp how to attack making it pick the correct max weight based on m/f. This is what I already have.

cboboxMF = lets the user choose male or female
cboboxheight=selects the height (in inches) from tblMale Ht/Wt Max
Max=selects the appropriate maximum weight from tblMale ht/wt max.

I need it to look at either tbl male ht/wt max OR tbl female ht/wt max depending on cboboxMF's value.

thanks for the help thus far.

 

[Dave Eyley]

If you want the 4th record to be on the main form you could just add the field as an unbound object and on the 2nd combo's AFTERUPDATE property write a piece of code like -

If Me!cboboxMF="Male" Then
Me!Field4=DLookUp("[MaxWeight]","[tblMaleht/wt]","[Maleht]=Forms!FormName!cboboxheight")
End If
If Me!cboboxMF="Female" Then
Me!Field4=DLookUp("[MaxWeight]","[tblFemaleht/wt]","[Femaleht]=Forms!FormName!cboboxheight")
End If


Field4 displays the result on the form (call it what you want)
[MaxWeight] is the fieldname in the table for MaxWeight
[Maleht] is the fieldname in the table for Male height.

The line between the If and End If lines is all one statement on one line. It's too long to get it like that here.

Does this help?

 

[jarheadjim]

?

what is field4 for then? I select the sex (m/f), I select the height, and field4=maxweight? this would go as an afterupdate event for height, right? I need an english to Marine dictionary so I can understand.

I did this: all the max weights are on one table now. the fields on that table are [marheight],[mweight] & [fweight]. sooo, now i just need it to select the appropriate column rather than a whole seperate table, does that make it easier, or do i need to put the tables back like i had them?

 

[jarheadjim]

GOT IT!!!!!!!!!

If Me![Male / Female] = "male" Then [MaxWeight] = [Height].Column(2)
If Me![Male / Female] = "female" Then [MaxWeight] = [Height].Column(3)

did what i needed in an afterupdate event for height. Thank you for your patience Dave.

 

[Dave Eyley]

I'm sorry about Field4. I hope it didn't confuse you for too long. I had written the code out on paper, then realised I had 1 field too many - what with all the maxweight, fweight, mweight, maleht, femaleht etc... so I lost one and forgot to rename them.

I think the one table solution is the best one.

It was an interesting problem for us newbies and all the better that you actually solved it youself. Feels good when it all comes together, doesn't it? Until the next problem, of course.

Look out for my next problem

Cheers

Dave E