Open a form from a field in a specific table

[toumack]

I have a table named "JewelryStyles" in that table I have 2 text field that contains names of form I want to open using VBA at a specific point, in my recordset, I have the name of form, so what should be the command to open that form from an rst?

Image:

Here is the code so far:

Private Sub STYID1_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Dim strSQL1 As String
strSQL1 = "SELECT JewelryStyles.StylesID, JewelryStyles.JewelryStyles, JewelryStyles.Styles, JewelryStyles.OrdreTri, JewelryStyles.FormForSizes, JewelryStyles.FormForColors  FROM JewelryStyles WHERE JewelryStyles.OrdreTri=1 ORDER BY JewelryStyles.OrdreTri"
Set rst = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset, dbSeeChanges)

Me.StyleCode = rst![JewelryStyles]

rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

DoCmd.OpenForm rst!FormForSizes

 

[arnelgp]

Private Sub STYID1_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Dim strSQL1 As String

Dim strForm As String
strSQL1 = "SELECT JewelryStyles.StylesID, JewelryStyles.JewelryStyles, JewelryStyles.Styles, JewelryStyles.OrdreTri, JewelryStyles.FormForSizes, JewelryStyles.FormForColors FROM JewelryStyles WHERE JewelryStyles.OrdreTri=1 ORDER BY JewelryStyles.OrdreTri"
Set rst = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset, dbSeeChanges)

Me.StyleCode = rst![JewelryStyles]
strForm=rst!FormForSizes & ""
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

if strForm <> "" Then
DoCmd.OpenForm strForm
End If

 

[toumack]

Thanks, your code will be useful if there is no name in the table for the form size, than continue with the color form.
Super!

 

[arnelgp]

you mean if the field FormForSizes is blank, use the field FormForColors?

…
…
strForm=rst!FormForSizes & ""
if strForm = "" then strForm = rst!FormForColors & ""
rst.Close
dbs.Close
...
…

 

[toumack]

I will test it tomorrow, it is 5 o'clock in the morning here… thanks again

Claude