combo box to show a form

[judeastone]

hello,

i would like to create a combo box, on a form, which will load *some* of the forms in my database, but unsure how to do it. previously i needed to show reports, and someone said this.. which works, and shows all reports:

SELECT [MSysObjects].[Name] FROM MSysObjects WHERE (Left([Name],1)<>"~") And ([MSysObjects].[Type])=-32764 ORDER BY [MSysObjects].[Name];

i just need some of them, is this possible?

thanks

jude

 

[Jack Cowley]

Using the function below and setting the prefix of a form name of those you want to see to spc will do what you want (i.e. spcMyForm). Call the function from the forms On Load event (i.e. Me.ComboboxName.RowSource = FormList()). Set the combo boxes Row Source Type to Value List.

Private Function FormList() As String
Dim db As DAO.Database
Dim i As Integer
Dim contr As Container
Dim strFormList As String, strFormName As String, StrFormProp

On Error GoTo HandleErr

Set db = CurrentDb
Set contr = db.Containers("Forms")

strFormList = vbNullString
For i = 0 To contr.Documents.Count - 1
strFormName = contr.Documents(i).Name
StrFormProp = contr.Documents(i).Properties("Description")

If Left$(strFormName, 3) = "spc" Then
If (Len(strFormList) > 0) Then strFormList = strFormList & ";"
strFormName = Mid$(strFormName, 4)
strFormList = strFormList & strFormName & ";" & StrFormProp
End If
Next i

FormList = strFormList

ExitHere:
On Error Resume Next

Set contr = Nothing
Set db = Nothing

Exit Function

HandleErr:
If Err.Number = 3270 Then
'StrFormProp = "No description available."
Resume Next
Else
MsgBox "FormList: " & Err.Description & vbCrLf & _
"from " & Err.Source & " (" & Err.Number & ")", vbCritical Or vbOKOnly, _
"Error Retrieving Form Lists"
End If
Resume ExitHere

End Function

hth,
Jack

 

[judeastone]

oh, you are so amazing!! fantastic.

one thing though... i'm in the final stages of producing my database now, and to change the form names would cause lots of other things to break, is there a way to easily adapt this script to call a subroutine or something which lists the forms it can list?

thanks an awful lot!

jude

 

[Jack Cowley]

To do what you want you have two choices as I see it. Use a program like Find and Replace or Speed Ferret to change form names throught the program OR add the form names to the Value List in the combo box and forget the code. Option two is what will probably work best in this situation.

Just curious. Why are you putting some form names into a combo box?

hth,
Jack

 

[judeastone]

Theres a few tables which contain data used elsewhere in the database, and ive put together a form to make a nice user interface for adding a new record, and one for ammending ones there...

As theres a few, it'd look best done from a drop down box..

If done this way, on row source type it lets me choose from table/query, value list, field... neither of which are appropriate... do i need value list, then build up the value list and its pointer elsewhere? How would i go about this?

Thanks

Jude

 

[Jack Cowley]

You use Value List and you build your list of form names, something like this:

"Form1";"Form2";"Form3"...

If you use the Wizard do not add the quotation marks as Access will do it for you.

In the After Update event of the combo box use code like this:

DoCmd.OpenForm Me.ComboBoxName

That should do it for you....

Jack

 

[judeastone]

Thanks

I've added these, and the coding to the "after update" part, which i customised to match my combo box name. and it gives this error when you click on an item...

"...Can't find the macro Do.Cmd"

Any ideas?

Thanks

 

[Jack Cowley]

Here is the answer I gave you in your PM:

What I sent you is 'code' so it needs to go into a subroutine.... Remove the line of code and select [Event Procedure] by clicking the down arrow at the left of the field. Click the button to the right with 3 dots. A code page will open and the cursor will be flashing between two lines of code. Add the DoCmd code there. Close the code page by clicking the X in the upper right and the code will now work.

Jack

 

[judeastone]

You're amazing!!

Thanks a lot.

I think i owe you a pint, or ten!

jude

 

[Jack Cowley]

You are welcome and a pint of Guinness it is! Next time I am over I will let you know... Last time I was there was during the reign of George V so you are pretty safe...

Continued success with your project....

Jack