Creating a Calculator UserForm - newb with lots of Q's

[a_madman]

I am new to this site and VBA... I have a small project I am working on to help make my job slightly easier. The form I need to create will do the following:

User first selects an aircraft at it's typical empty weight (no options).
User then selects the desired options.
User then clicks a "Calculate" command button at the bottom of the form which will sum the empty weight and all the options and display that total weight on the form.

Once I get this far I will work on another command button that will send all this information to a word document creating a summary report.

Question #1 - How do I get the weights into variables

The data will be coming in from a database and displayed on the form in combo and list boxes. I have this working successfully. However, I'm not sure what to do with the actual weights associated with each option. The weights themselves do not need to be shown on the form, but the code pasted at the end of this post does send both the option name and weight to the combo boxes on the form (I think I read something somewhere about showing one thing on the form, but having a different value assigned?).

Regardless, what I need to figure out is how to deal with the weights of the selected options. First problem is there will be an unkown number of options selected each time the form is used. Is there a way to create a new variable for each option that is selected and assign the weight to that variable? Or do I need to just declare a bunch of variables that will cover the most options that could be selected?

Second problem is how do I actually assign the weight to these variables? The data coming in is from tables in an Access database. Each table has 3 fields, with the weight being in the 3rd field.

Question #2 - How do I code the "Calculate" command button

First of all, is this even possible to keep the form open and have a field that is changed by clicking a calculate button?

Once question #1 is answered and I have variables with the weights of each selected option, how do I now sum these weights and when the user clicks the "Calculate" button send the total weight be displayed on the form? What kind of field do I need to have on the form for the data to be displayed in?



Thanks in advance for any help! I'm sure I will have many more questions as I figure things out, and I'll try to keep everything documented here so perhaps future users can get some of their own questions answered as well.





Full code (please ignore cmdOK for now, I'll work on that later)


Private Sub UserForm_Initialize()
On Error GoTo UserForm_Initialize_Err

Dim cnn As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
Dim rst3 As New ADODB.Recordset
Dim rst4 As New ADODB.Recordset
Dim rst5 As New ADODB.Recordset
Dim rst6 As New ADODB.Recordset
Dim i As Integer


Rem Open Database
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=S:\Jens\Single Engine\Caravan Weight.mdb"

Rem Query for Aircraft Name
rst1.Open "SELECT [Aircraft Name], [Aircraft Standard Empty Weight] " & _
"FROM Aircraft ORDER BY [Aircraft Name];", _
cnn, adOpenStatic

rst1.MoveFirst

i = 0

With Me.cboAircraftName
.Clear

Do
.AddItem
.List(i, 0) = rst1![Aircraft Name]
.List(i, 1) = rst1![Aircraft Standard Empty Weight]

i = i + 1
rst1.MoveNext
Loop Until rst1.EOF
End With

Rem Query for Avionics Package
rst2.Open "SELECT [Equipment Name], [Equipment Weight] FROM AvionicsPackage " & _
"ORDER BY [Equipment Weight];", _
cnn, adOpenStatic

rst2.MoveFirst

i = 0

With Me.lstAvionicsPackage
.Clear

Do
.AddItem
.List(i, 0) = rst2![Equipment Name]
.List(i, 1) = rst2![Equipment Weight]

i = i + 1
rst2.MoveNext
Loop Until rst2.EOF
End With

Rem Query for Avionics Options
rst3.Open "SELECT [Equipment Name], [Equipment Weight] FROM AvionicsOptions " & _
"ORDER BY [Equipment Weight];", _
cnn, adOpenStatic

rst3.MoveFirst

i = 0

With Me.lstAvionicsOptions
.Clear

Do
.AddItem
.List(i, 0) = rst3![Equipment Name]
.List(i, 1) = rst3![Equipment Weight]

i = i + 1
rst3.MoveNext
Loop Until rst3.EOF
End With

Rem Query for General Options
rst4.Open "SELECT [Equipment Name], [Equipment Weight] FROM GeneralOptions " & _
"ORDER BY [Equipment Weight];", _
cnn, adOpenStatic

rst4.MoveFirst

i = 0

With Me.lstGeneralOptions
.Clear

Do
.AddItem
.List(i, 0) = rst4![Equipment Name]
.List(i, 1) = rst4![Equipment Weight]

i = i + 1
rst4.MoveNext
Loop Until rst4.EOF
End With

Rem Query for Cargo Options
rst5.Open "SELECT [Equipment Name], [Equipment Weight] FROM CargoOptions " & _
"ORDER BY [Equipment Weight];", _
cnn, adOpenStatic

rst5.MoveFirst

i = 0

With Me.lstCargoOptions
.Clear

Do
.AddItem
.List(i, 0) = rst5![Equipment Name]
.List(i, 1) = rst5![Equipment Weight]

i = i + 1
rst5.MoveNext
Loop Until rst5.EOF
End With

Rem Query for Interior
rst6.Open "SELECT [Equipment Name], [Equipment Weight] FROM Interior " & _
"ORDER BY [Equipment Weight];", _
cnn, adOpenStatic

rst6.MoveFirst

i = 0

With Me.lstInteriorOptions
.Clear

Do
.AddItem
.List(i, 0) = rst6![Equipment Name]
.List(i, 1) = rst6![Equipment Weight]

i = i + 1

rst6.MoveNext
Loop Until rst6.EOF
End With


UserForm_Initialize_Exit:
On Error Resume Next
rst1.Close
rst2.Close
cnn.Close

Set rst1 = Nothing
Set rst2 = Nothing
Set rst3 = Nothing
Set rst4 = Nothing
Set rst5 = Nothing
Set rst6 = Nothing
Set cnn = Nothing
Exit Sub

UserForm_Initialize_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume UserForm_Initialize_Exit
End Sub
Private Sub cmdOK_Click()
Application.ScreenUpdating = False

With ActiveDocument
.Bookmarks("AircraftName1").Range.Text = cboAircraftName.Value
.Bookmarks("AircraftName2").Range.Text = cboAircraftName.Value
.Bookmarks("BOW").Range.Text = 0
.Bookmarks("EmptyWeight").Range.Text = 0
.Bookmarks("Option1").Range.Text = lstAvionicsPackage.Value
.Bookmarks("Option1Weight").Range.Text = 0
.Bookmarks("Option2").Range.Text = lstAvionicsOptions.Value
.Bookmarks("Option2Weight").Range.Text = 0
.Bookmarks("Option3").Range.Text = lstGeneralOptions.Value
.Bookmarks("Option3Weight").Range.Text = 0
.Bookmarks("Option4").Range.Text = lstCargoOptions.Value
.Bookmarks("Option4Weight").Range.Text = 0
.Bookmarks("Option5").Range.Text = lstInteriorOptions.Value
.Bookmarks("Option5Weight").Range.Text = 0
End With

Application.ScreenUpdating = True
Unload Me
End Sub
Private Sub cmdCancel_Click()
Unload Me

ActiveDocument.Close SaveChanges:=False
End Sub
Private Sub cmdCalc_Click()
End Sub


Edited 29-Jun-07 by geekgirlau. Reason: insert line breaks

[a_madman]

I've gotten a little farther today. Maybe my first post was to broad to get a quick answer. Here's a more specific question.

I'm using listbox's that are set to show the value of the first column, but maintain the value of the second column, which is a weight. So on the form, you see the name of an option, but the value of that selection is it's weight (not shown on form). I need to sum the weights together, but I can't get my addition formula (see below) to work if the listbox's are set to multiselect. And I don't know how to deal with the variables if I use multiselect because I don't know how many options will be selected.

Any suggestions?

Private Sub cmdCalc_Click()

Rem Print Empty Weight to Weight Summary
Dim EmptyWeight As Integer
EmptyWeight = cboAircraftName.Value
txtEmpty.Text = EmptyWeight & " lbs"

Rem Print total Options Weight to Weight Summary
Dim OptionWeight As Integer
Dim Option1 As Integer
Dim Option2 As Integer
Dim Option3 As Integer
Dim Option4 As Integer
Dim Option5 As Integer
Option1 = lstAvionicsPackage.Value
Option2 = lstAvionicsOptions.Value
Option3 = lstGeneralOptions.Value
Option4 = lstCargoOptions.Value
Option5 = lstInteriorOptions.Value
OptionWeight = Option1 + Option2 + Option3 + Option4 + Option5
txtOptions.Text = OptionWeight & " lbs"

Rem Print new BOW to Weight Summary
Dim BOW As Integer
BOW = EmptyWeight + OptionWeight
txtBOW.Text = BOW & " lbs"

End Sub

[mdmackillop]

Hi
Welcome to VBAX,
Is it possible to post a small sample with some data to test fly your code. You can attach files by zipping and using Manage Attachments in the Go Advanced section.
Regards
MD

[a_madman]

Attached is both the .dot file and .mdb associated with it. You'll have to edit the database location I guess... and I've been messing with the code a little today so it's a little different than what you've seen I guess.

[mdmackillop]

OK, thanks for posting.
I'm getting an error with adOpenStatic, and I'm missing FlightProfiles.dot. The same thing? What references are you using in your project?

[a_madman]

Since I am such a noob at this... I guess I don't know how to keep these projects seperate. Whenever I open VBA on the top left there's a list of multiple projects that have been created by various people I work with. The FlightProfiles.dot is one of those projects but doesn't have anything to do with my project. I don't know why that other stuff is in there...

[lucas]

This looks like a call to another procedure...can you tell us what it does(can we comment it out so we can look at your form) or provide the procedure?
adOpenStatic

[Norie]

lucas

As far as I know that's an ADO constant.

[lucas]

ah access thing....I show no references missing either except the flight profile.dot....same as Malcolm

[a_madman]

So are those files working for you or do I need to edit something and re-post it? Is there a place that should list all the references in a file?

[fumei]

Don't know if this will help at all. I am attaching a very simple userform. You can display it with the "Test Calculator" text icon on the top toolbar.

Select items from the listbox, and then click Calculate. Calculate displays the selected items, their individual weight, and a total weight in a Label.

It is completely dynamic, so if you unselect an item and click Calculate, the Label will display what is CURRENTLY selected.

If you click Calculate and nothing is selected in the listbox, the Label displays...."Nothing selected."

Essentially the array of weights matches the array of item names.