Increment form open cbo from unbound table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have twenty forms that need to open and close in order (increment by 1)
using an On_Click event. I added a table named TableOfForms with a FormNo
field (in opening order) and FormName Field. I got the form that is open to
get the current form name and number , but now I can't figure out how to open
the next form number in the TableOfForms...it is not bound to this
form...tried a couple of ways but my little brain is just not getting there
today. Thought I saw a post where you can't open a form from a table?? Do I
have to open a recordset to get the value? I'm not very good at that level
yet. Thanks for any help.

intFormNbr = DLookup("FormNo", "TableOfForms", "Form.Name =[FormName]")
intFormNbr = intFormNbr +1

DoCmd.Open Form "get the formname from tableofforms.column(1) where
intformnbr = tableofforms.column(0) aaaaarrrrggggg!"
 
Hello Maarkr.

Maarkr said:
I have twenty forms that need to open and close in order
(increment by 1) using an On_Click event. I added a table named
TableOfForms with a FormNo field (in opening order) and FormName
Field. I got the form that is open to get the current form name
and number, but now I can't figure out how to open the next form
number in the TableOfForms...it is not bound to this form...tried
a couple of ways but my little brain is just not getting there
today. Thought I saw a post where you can't open a form from a
table?? Do I have to open a recordset to get the value? I'm not
very good at that level yet. Thanks for any help.

intFormNbr = DLookup("FormNo", "TableOfForms", "Form.Name =[FormName]")
intFormNbr = intFormNbr+1

DoCmd.Open Form "get the formname from tableofforms.column(1) where
intformnbr = tableofforms.column(0) aaaaarrrrggggg!"
Click to expand...

You could simply get the name of the form with a DLookup:
DLookup("FormName", "TableOfForms", "FormNo=" & intFormNbr)

And of course, you could use a recordset instead:

Dim strSQL as String, strNextForm as String
strSQL = "Select FormName From TableOfForms Order By FormNo"
With CurrentDb.OpenRecordset(strSQL, dbOpenFormwardOnly)
.FindFirst BuildCriteria("FormName", dbText, Me.Name)
If Not .NoMatch Then
.MoveNext
if Not .EOF Then strNextForm = !FormName
End If
.Close
End With
If strNextForm <> vbNullString Then DoCmd.OpenForm strNextForm
 
The old "double DLookup" trick. new it wasn't too hard...thanks

Wolfgang Kais said:
Hello Maarkr.

Maarkr said:
I have twenty forms that need to open and close in order
(increment by 1) using an On_Click event. I added a table named
TableOfForms with a FormNo field (in opening order) and FormName
Field. I got the form that is open to get the current form name
and number, but now I can't figure out how to open the next form
number in the TableOfForms...it is not bound to this form...tried
a couple of ways but my little brain is just not getting there
today. Thought I saw a post where you can't open a form from a
table?? Do I have to open a recordset to get the value? I'm not
very good at that level yet. Thanks for any help.

intFormNbr = DLookup("FormNo", "TableOfForms", "Form.Name =[FormName]")
intFormNbr = intFormNbr+1

DoCmd.Open Form "get the formname from tableofforms.column(1) where
intformnbr = tableofforms.column(0) aaaaarrrrggggg!"
Click to expand...

You could simply get the name of the form with a DLookup:
DLookup("FormName", "TableOfForms", "FormNo=" & intFormNbr)

And of course, you could use a recordset instead:

Dim strSQL as String, strNextForm as String
strSQL = "Select FormName From TableOfForms Order By FormNo"
With CurrentDb.OpenRecordset(strSQL, dbOpenFormwardOnly)
.FindFirst BuildCriteria("FormName", dbText, Me.Name)
If Not .NoMatch Then
.MoveNext
if Not .EOF Then strNextForm = !FormName
End If
.Close
End With
If strNextForm <> vbNullString Then DoCmd.OpenForm strNextForm
Click to expand...
 
Back
Top