Auto Populate based on Combo box

[Mpike926]

Hi all,
I will do the best I can to explain this, but here is what I need to do.
I have two tables, LoanGeneral and Approval. On the loan general table, there are fields for acres, units, square feet, for the property(among other fields). What I want to do is be able to choose a Unit type from a Combobox, and then automatically have a subsequent box fill with what was selected in the combo box, specific to that asset

So if in the combo box you chose acres, I would want the subsequent box to show the total acres for that asset
If they chose Units, I would want the number of units to fill in


I dont know if that makes sense but I did my best to explain :P

Thanks for the help

[Rod]

Is this for a single form or a continuous form? Is it the same textbox that is to be populated with the units or acres, etc?

[Mpike926]

Rod, here is a screen shot -> http://oi50.tinypic.com/11rg9l2.jpg

its just a single form, just simple text boxes. there are four boxes, one for acres, units, gross sqft and rentablesqft
What I want to do is have one combo box where some one could choose either acres units gsqft or rsqft and then below that combo box, the unit selected would appear for that property. This seems like a pointless task but it needs to be done for some reporting I have

[Rod]

Nice looking application!

Are you wedded to a combo box? This can be managed with a combo box, a list box (not nice) or an option group. I rather like the idea of an option group of toggle buttons because I think it would fit well with your look and feel.

Anyway, the logic is always the same. I think for you, rather than managing an unbound textbox and label for the chosen measurement, it would be cleaner and easier to manipulate the visibility of a stack of four bound textboxes. I see you already have the textboxes so the design work will be minimal. (Note when you hide a textbox its label is also hidden.)

Here's a picture of how it might look - OK, use your imagination.

• Stack the existing textboxes one on top of the other. You will now have to use the Property Sheet combo box if you want to select and/or change their properties.
• Use the control wizard to build an option group of toggle buttons. Change the text size, button size, button position (must remain inside the box but then you can adjust the box boundaries), etc.,etc.
• Superimpose the stack of textboxes on top of the option group (send the option group to the back if necessary).

That's it for the design. Now an indication of the programming necessary.

In the form module define a new sub procedure, perhaps naming it SynchroniseMeasurement. Here's an example of what it might look like.

Private Sub SynchroniseMeasurement()
    Select Case Me.optMeasurement
    Case 1
        Me.txtUnit.Visible = True
        Me.txtGSqFt.Visible = False
        Me.txtRSqFt.Visible = False
        Me.txtAcres.Visible = False
    Case 2
        Me.txtUnit.Visible = False
        Me.txtGSqFt.Visible = True
        Me.txtRSqFt.Visible = False
        Me.txtAcres.Visible = False
    Case 3
        Me.txtUnit.Visible = False
        Me.txtGSqFt.Visible = False
        Me.txtRSqFt.Visible = True
        Me.txtAcres.Visible = False
    Case 4
        Me.txtUnit.Visible = False
        Me.txtGSqFt.Visible = False
        Me.txtRSqFt.Visible = False
        Me.txtAcres.Visible = True
    End Select
End Sub

You must use your own control names.

Now you need to call this sub procedure from two events: the form's On Current event and the option group's Before Update event (could use After Update - same, same) If you don't know how to do this then repost.

[Mpike926]

Rod-
Thanks for the compliment on the database. It has taken many hours to get it to this point.
Just a few questions/clarifications.
I have never used the Option group. I added it the xyz button in the box to the form, proceeded through the wizard and then got something similar to what you have. I am assuming the Text13 box is just a simple text box that you 'imposed' into the option group.
I selected the Units that i have black boxed in my picture, and stacked them, but I am not quite sure what you mean as far as superimposing it into the option group.
and for using my own control names you basically mean

Private Sub SynchroniseMeasurement() Select Case Me.optMeasurement Case 1 Me.NumUnits.Visible = True Me.Gross Sqft.Visible = False Me.Rentable Sqft.Visible = False Me.Acreage.Visible = False
etc etc etc... correct?

[Rod]

I am assuming the Text13 box is just a simple text box that you 'imposed' into the option group

Correct. I'd rather pedantically use the phrase 'onto the option group' as the textbox has nothing to do with the option group.

I am not quite sure what you mean as far as superimposing it into the option group

Nothing sinister - just move it over the option group as I did with my example Text13. If the Back Style of the option group is Normal then you may have to Send It To Back (Arrange ribbon) to be able to see it. (In your terminology, the option group and stack of textboxes occupy the same real estate - joint tenants?

Private Sub SynchroniseMeasurement()
Select Case Me.optMeasurement
Case 1
Me.NumUnits.Visible = True
Me.Gross Sqft.Visible = False
Me.Rentable Sqft.Visible = False
Me.Acreage.Visible = False
etc etc etc... correct?

Looks OK to me. When you used the option group wizard you should have noted the values for each button but if you used the defaults then the first button is 1, the second 2, etc.

[Mpike926]

NEVER MIND--- Figured out this question and this was the only way i could delete it. (Edit it down to nothing)

[Mpike926]

Rod- This is what Ive got so far
So the control source of my option box is UnitsSelected. When I click a button, in the UnitsSelected box, the number 1,2,3,4 displays representing what button is selected. Also I have the Unit text fields stacked one on top of another, so when you click on acres, just the acres shows, and when you choose SqFt, that shows, basically that one becomes visible and the others stay 'hidden.' <- Im good up to this point
Is there a way to have UnitsSelected be where the chosen unit goes, instead of merely showing the one unit type while keeping all of the others hidden? I am wondering this because if that could be done, I could just add that one field onto the report, and the report would figure itself out. Basically what im looking for to happen in the below report is you choose acres, 6.72 displays, you choose sqft, that unit displays. (right now acres is hard coded in there, its the only thing we currently calculate by, hence me making this change to better accommodate property types based on primary collateral.) where the black lines are, the left column shows the entire balance/appraised value etc, and the right column calculates the right column per unit.
Once again thanks for the help. I got thrown onto access when our company decided we needed a better platform to track assets besides excel, therefore I have taught myself everything I know... Maybe I should take a class lol

[Rod]

I'll get back to you soonest, perhaps with some examples. Seriously, consider acquiring a text on Access - Amazon has some good ones available. I have used the one from Wrox to advantage for many years.

[Rod]

Yes, it's possible. You need to add another attribute to your table to store the selected text. Then choose an event (if the option group is unbound then you need two) and save an appropriate text string into the new column (attribute). The column need not be shown on the form as a control! Here, off the top of my head is a sub procedure to do what you want assuming the new attribute is called PreferredMeasurement.

Private Sub PopulatePreferredMeasurement()

Select Case Me.<my option group name>
Case 1
  PreferredMeasurement = "Units"
Case 2
  PreferredMeasurement = "Gross Sq. Ft."
Case 3
  PreferredMeasurement = "Rentable Sq. Ft."
Case 4
  PreferredMeasurement = "Acres"
End Select

End Sub

Invoke this from the form's before update event (and from the option group's before update event if the option group is unbound).

Of course there are many variations on this theme. If these four measurements are a common feature throughout your project then it may be worthwhile formalising routines to handle them (enummerations, common procedures, etc.)

As a different approach I have constructed a prototype database for you. This stores the option group value but not the text. The text is retrieved via a common routine as and when it is needed.

MSAFOptionGroup.accdb

Note: I see your 'report' is in fact a form and have assumed this in my reply.

[Mpike926]

Rod,
The 'Approval Form' Is just called approval form,. its not necessarily stating that it is a form, thats jut the lingo in our office we throw around when we need an approval form done.
It is infact a report. And as far as the rest goes. Im just confused as shit. Nice work you have, and it looks good, because it works on your database, however I just cant figure out what to do. maybe ill take a class and learn vba or whatever, Im just frustrated and I want to break my computer. ugh

[Mpike926]

So I Got It. I am so excited. I cannot thank you enough. We should look into hiring you as a consultant haha
Rod, seriously tho, this is a much needed improvement and I am so glad you were able to help. I was able to reflect the correct fields on the report form and everything. sweet deal man!

[Rod]

We should look into hiring you as a consultant haha

You can't afford me! Glad you figured it out.

[Mpike926]

Lol, you might be surprised. Rod, since you were so good at helping me with this, I have another thread where I posted a question, I think its much easier, but still dont know how to go about it. maybe you could check it out here: https://www.accessforums.net/access/...ery-27544.html

[Rod]

Mac,

I'm off to bed now - nearly midnight here. I'll look into this tomorrow but there's a question of protocol since June7 is involved with answering this requirement. I clear it with him first. (Might be her - who knows?)