Open a Form with a form Name that use a Wildcard

[ted.martin]

I have a series of forms which I have named (say) F1-XXX, F2-YYY and F3-ZZZ. As I want them to open in sequence I have put two command buttons on the form, one cmdPrevious and one cmdNext.

The alpha part of the form name will be vary from form to form. If form F1-XXX is open, when I press cmdNext I want form F2-YYY to open.

If there was a command like Docmd.OpenForm Like "F2*" then this would do it but of course there isn't.

The only thing I can think of is store all the form names in a table with 2 fields (FormID and FormName) and essentially do a DLOOKUP to find the next form name.

e.g. Docmd.OpenForm (Dlookup("[FormName]","myForms","FormID = 'F2'"))

Can anyone think of a quicker way to open a form with a wildcard in the Form name?

 

[vbaInet]

ted, just build the form names. Let's say there's a variable i that increments, then you can do something like:

Dim i as Byte

i = i + 1
Docmd.openform "F" & i & "YYY"

But it seems that XXX and YYY are not the same text? If that's the case then you would need to use a multidimensional array to associate the numbers with the form names.

 

[ted.martin]

Thanks - you are right the XXX and YYY are different. It works fine with the DLookup in a Master Table which I guess is not too far off using an array as I would have to dimension that anyway.

Will have a play - so thanks.

Here is the code I am using, which will be 'generic' in each form.

In this TEST example, a typical form name could be
F10-FormName10
F11-FormName11

the myForms table is:
FieldID Form Name
10 F10-FormName10
11 F11-FormName11

Naturally I will be more creative/descriptive with the form name in the text part after the -





Option Compare Database
Option Explicit
Dim iForm As Integer

Private Sub cmdNext_Click()

iForm = Mid(Me.Name, 2, InStr(1, Me.Name, "-", vbTextCompare) - 2) + 1

If Not IsNull(DLookup("[Form Name]", "myForms", "FormID = " & iForm)) = True Then
Application.Echo False
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm DLookup("[Form Name]", "myForms", "FormID = " & iForm)
Application.Echo True
Else
MsgBox "This is the Last form of this Set", vbInformation, "Will close anyway"
DoCmd.Close acForm, Me.Name
End If

End Sub

Private Sub cmdPrevious_Click()

iForm = Mid(Me.Name, 2, InStr(1, Me.Name, "-", vbTextCompare) - 2) - 1

If Not IsNull(DLookup("[Form Name]", "myForms", "FormID = " & iForm)) = True Then
Application.Echo False
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm DLookup("[Form Name]", "myForms", "FormID = " & iForm)
Application.Echo True
Else
MsgBox "This is the First form of this Set", vbInformation, "Will remain here!"
End If

End Sub

Private Sub Form_Open(Cancel As Integer)
Me.Caption = Mid(Me.Name, InStr(1, Me.Name, "-", vbTextCompare) + 1) & " " & Mid(Me.Name, 2, InStr(1, Me.Name, "-", vbTextCompare) - 2) & " of " & intForm
lblTitle.Caption = Mid(Me.Name, InStr(1, Me.Name, "-", vbTextCompare) + 1)
DoCmd.Maximize
End Sub

 

[vbaInet]

The DLookup() just seems to be too much for this task. What you can do is use a recordset to read off the names of the forms with their numbers into an associative array and use the array. Rather than having to query the db everytime. So the array would look like:

1         F1-Formname1
2         F2-Formname2

It also seems that the numbers are the last characters of the form name.

Remember to always put your code in code tags. I'm sure I've informed you of this before

 

[ted.martin]

Got it - Ok - thanks very much. Will try the array route. I understand what you mean. Forget the number at the end of the formname - it will always be an alpha. the idea is the formname part will be 'what the form actually does' in data collection terms.

My project will involve about 10 different data collection forms and I wanted some generic code to use to move successively from 1 form to the next.

 

[vbaInet]

And a tab control doesn't cut it for you?

 

[ted.martin]

No, as each form may even have tabs and subforms - from a user point of view, need each form to be quite separate BUT in a defined sequence.

 

[vbaInet]

Alrighty! See how the array goes.