UtterAccess Discussion Forums > how to determine user selection before displaying userform

coolboyzz

Oct 24 2005, 05:28 AM

hi again,

i want to determine the user selection before displaying userform.

Basically, user is given the choice to choose between the followings (through command buttons in Sheet 1)

1. Modify Database
2. Create Report

there are forms associated with option 1 and 2 plus there is one more form that acts as a go between the user clicking the command button in excel sheet and the display of the appropriate form. This go between form is Choose Database form.

Because both options require user to choose database, im having difficulties in determining which option the user chose, so that when the user has chosen the database, the appropriate form will be displayed.

Here are snippets of the codes currently behind the all those buttons and form:

This is from Sheet 1

CODE

Private Sub cmdModifyDB_Click()
Dim searchExt As String
Dim searchResult As Variant
Dim i As Integer

searchExt = ThisWorkbook.Path & "\*.mdb"
searchResult = GetFileList(searchExt)

Select Case IsArray(searchResult)
        Case True 'files found
            For i = LBound(searchResult) To UBound(searchResult)
                ChooseDBForm.lbDBSelected.AddItem searchResult(i)
            Next i
            ChooseDBForm.Show
        Case False 'no files found
            MsgBox "No Database Exists"
            ChooseDBForm.Hide
End Select

End Sub

Private Sub cmdCreateReport_Click()
Dim searchExt As String
Dim searchResult As Variant
Dim i As Integer

searchExt = ThisWorkbook.Path & "\*.mdb"
searchResult = GetFileList(searchExt)

Select Case IsArray(searchResult)
        Case True 'files found
            For i = LBound(searchResult) To UBound(searchResult)
                ChooseDBForm.lbDBSelected.AddItem searchResult(i)
            Next i
            ChooseDBForm.Show
        Case False 'no files found
            MsgBox "No Database Exists"
            ChooseDBForm.Hide
End Select

End Sub

'These are the codes behind ChooseDBForm

CODE

Private Sub cmdCancel_Click()
Unload ChooseDBForm
End Sub

Private Sub cmdOK_Click()

  'check if database is selected from the listbox
  If lbDBSelected.listIndex = -1 Then
    MsgBox "You have not selected any database to be accessed. Please choose from the database(s) listed above"
  Else
    ModifyRecordsForm.lblDBName.Caption = lbDBSelected.Value
    DBPath = ThisWorkbook.Path
    DBName = "\" & lbDBSelected.Value
    DBFullPathName = DBPath & DBName

    Unload ChooseDBForm
    OpenConn (DBFullPathName)
    PopulateRecords

     ModifyRecordsForm.Show
  End If
End Sub

As you can see, i have not taken into account if the user chooses to create report instead, coz i do not know how to.....

thank you sooo much!

ted.

KingMartin

Oct 24 2005, 10:43 AM

Hi ted,

it may not be my day but I am not 100% sure I understand your question...

If you choose one option in form1 and open a form2 by clicking on an OK button on form 1, you want the value from form1 to be inherited by some control in form2 - is that your problem?

Several ways:

1) Hide the form1, not Unload (so you can still read the values from its controls and unload it when you don't need it)

2) Save the value in a modul level variable, read it in Userform2_Initialize event.

3) Save the value somewhere in your workbook, again, read it in as in 2)

If this is not your problem, please explain

Martin

KingMartin

Oct 24 2005, 10:43 AM

Hi ted,

it may not be my day but I am not 100% sure I understand your question...

If you choose one option in form1 and open a form2 by clicking on an OK button on form 1, you want the value from form1 to be inherited by some control in form2 - is that your problem?

Several ways:

1) Hide the form1, not Unload (so you can still read the values from its controls and unload it when you don't need it)

2) Save the value in a modul level variable, read it in Userform2_Initialize event.

3) Save the value somewhere in your workbook, again, read it in as in 2)

If this is not your problem, please explain

Martin

KingMartin

Oct 24 2005, 10:43 AM

Hi ted,

it may not be my day but I am not 100% sure I understand your question...

If you choose one option in form1 and open a form2 by clicking on an OK button on form 1, you want the value from form1 to be inherited by some control in form2 - is that your problem?

Several ways:

1) Hide the form1, not Unload (so you can still read the values from its controls and unload it when you don't need it)

2) Save the value in a modul level variable, read it in Userform2_Initialize event.

3) Save the value somewhere in your workbook, again, read it in as in 2)

If this is not your problem, please explain

Martin

coolboyzz

Oct 24 2005, 12:03 PM

hi Martin,

sorry for my terrible explanation of the problem. after i reread my post, i understand why you got so confused... (i have been working for this thing for a couple of days straight - for a vb virgin, having too much dim and redim can cause serious health problems

)...

basically there are three forms:

1. Modify Database Form
2. Create Report Form
3. Choose Database Form

and there are Two Command Buttons on Excel Sheet.

The First command button is to Modify Database and the second is to Create Report .

When the user clicks either Modify Database or Create Report command buttons, the user will be presented with Choose Database Form (i want to make this a default form for both modifying records and creating reports). Upon choosing the database, and clicking the OK button on the Choose Database Form, the user should then be presented with either Modify Database Form or Create Report form, according to their initial click of the command buttons on excel sheet.

So in short, i do not know how to determine which button the user has clicked before showing either ModifyDatabaseForm or CreateReportForm because there is another form in between i.e. ChooseDatabaseForm. How do i transfer the 'value' of that click and take it through to ChooseDatabaseForm before displaying the eventual form?

I hope my attempt to explain the problem is satisfactory.....(if not, you can shoot me!)

Thanks again Martin, I really do appreciate the effort! This forum has been the lifeline of my project!

cheers,

ted.

KingMartin

Oct 24 2005, 04:39 PM

Hi again ted,

see attached.

Let me know if it's close to your needs.

There are two public variables in Module1. One remembers which button you clicked and the second remembers the chosen database...

Martin

coolboyzz

Oct 25 2005, 08:12 AM

hi Martin,

Thanks a lot... such a simple solution!!

I really do appreciate your help. You are a legend!

cheers mate,

ted.

KingMartin

Oct 25 2005, 08:49 AM

You're
1) too kind
2) welcome

Martin