Using combo box to open subform

[zarkov4995]

Hi guys,

I've spent half a day browsing the archives but I can't find a solution to my problem:

I got a main form with a alphabetically sorted combo box where I want to open a specific subform, based on the selection in the cbo. I can do it in an ugly way, using the primary key from the table used to populate the cbo, but I would like to use one of the visible columns in the cbo instead to make it clearer.

That is, instead of this:

Private Sub cboFruitType_AfterUpdate()
Select Case Me.cboFruitType.Value
Case 1
Me.Form4.SourceObject = "tblApples"
Case Else
Me.Form4.SourceObject = "frmPleaseChoose"
End Select
End Sub

I would like this:
Private Sub cboFruitType_AfterUpdate()
Select Case Me.cboFruitType.Value
Case "Apples"
Me.Form4.SourceObject = "tblApples"
Case Else
Me.Form4.SourceObject = "frmPleaseChoose"
End Select
End Sub

How do I get anything else than the primary key from the combobox? I guess VBA is the answer, but I can't figure out how...

 

[RuralGuy]

You can select any column in the ComboBox with the zero based:
Me.cboFruitType.Column(2)
Column(0) is the 1st column, Column(2) is the 3rd column, etc.

 

[zarkov4995]

Thanks Ruralguy!

It works, and I will go for that. One thing that confuses me though, is that using Me.cboFruitType.Column gives me the item with primary key tblFruits.ID=n, not the n:th item in the list (which is sorted alphabetically). I guess this is better though...

Thanks again!

 

[RuralGuy]

You're welcome. Glad I could help.

 

[fibayne]

Opening subfiorm via combo box selection

Hi

Having searched and read through various postings this one seems to be what I'm looking for, being a fairly basic user I cant get my head round where to put the form/table names from my db into this statement to get it to work, could you point me in the right direction ..once again

Main form is 'frmVMPOrder'
Subforms are 'sfOnline, sfDirectories, sfPress'
Combo Box is 'MediaType' based on 'tblMediaType' where column 2 is 100, 200, 300 with Column 3 being Online, Directories, Press (do i need column 3 ??)

So if 100 is selected sfOnline ''will'' open

thanks in advance for any help
Fi

 

[fibayne]

Try not to laugh..

In relation to my post about opening a subform specific to a selection made in a combo box on the main form would something like this ever work (assuming the correct code & syntax both of which are beyond me..were used....) as always any response would be more than welcome
Fi

IIf([MediaDealType]='100',[Forms]![ sfOnline],IIf([MediaDealType]='200',[Forms]![ sfPress]))

 

[RuralGuy]

You should really start a new thread with your questions. FYI a SubForm is a form that is displayed on another form by means of a SubFormControl. It sounds like you are really talking about opening another form (a second form)from a ComboBox which is certainly possible.

 

[fibayne]

Opening subfiorm via combo box selection

Hi Rural Guy
thanks for your reply apologies for not starting a new thread, will do in the future,
I thought the original thread seemed to be almost what I wanted which is

It is basically an Orders DB, on the main form POrders when a new order number is selected a subform opens up where the order detail can be entered and the PO relevant to the supplier of that particular line on the subform is produced plus the Client Order form

I have a scenario where there are currently 4 formats of Orders each with its specific calculations built into the relevant subform, I could from the switchboard for example select either of the 3 scenarios to take me into an order specific to that type but I would rather if it is possible have only one option on the Switchboard to minimize input errors and where the user cna select from a combo box which format they wnat to raise an order against when that is selected the relevant subform will open with all the realated calculations for that type of order, do you think this is possible from your replay to the original thread it sounds as if it can but not knowing coding very well (at all...) I do not know how to apply it to my DB I have tried replacing my frm and field names in where it looks like they shouls go but I can get it to work ....

if you can help would be great thanks agian
Fi

 

[RuralGuy]

As I stated earlier, are you opening a 2nd form or just making a SubForm on the POrders form visible? Do you want the ComboBox to do the work or are you using some other control to "open the SubForm"?

 

[fibayne]

on my last post I had Me. infront of the subform names I was trying to see if that made any diff to the runtime error it didnt infact it made it much worse so that has been removed and I am back to just runtime error 424 'Object Not Found'

PLEASE IGNORE THIS POST

 

[fibayne]

Hi Rural Guy

apologies for not answering correctly,

Main Form is POrder the Combo Box is on the main form its RowSource Type is set to List and the Value List is "Online";"Press";"Directories";

I am trying to make the forms visible depending on the selection made in the combo rather than opening asnew form

I am trying to get the Combo box to do all the work using AfterUpdate, I did in the meantime find the code below which I tried to adapt to fit my scenario but am getting Run Time error 424 Object Required

I really apprecaite you time and help

-------------------------------------------------------------------------
Sub ShowSubform()

'Save unsaved changes to currently open subform
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'Display appropriate subform based on MediaType chosen
If MediaType = "Online" Then
sf_VMPress.Visible = False
sf_VMDirectories.Visible = False
sf_VMOnline.Visible = True

ElseIf MediaType = "Press" Then
sf_VMPress.Visible = True
sf_VMDirectories.Visible = False
sf_VMOnline.Visible = False

ElseIf MediaType = "Directories" Then
sf_VMPress.Visible = False
sf_VMDirectories.Visible = True
sf_VMOnline.Visible = False

Else
sf_VMOnline.Visible = False
sf_VMPress.Visible = False
sf_VMDirectories.Visible = False

End If

End Sub

Private Sub cmdClose_Click()

'Close form
DoCmd.close

End Sub

Private Sub Form_Current()

'Call subroutine to display appropriate subform based on MediaType
ShowSubform

End Sub

Private Sub MediaType_AfterUpdate()

'Call subroutine to display appropriate subform based on MediaType
ShowSubform

End Sub

________________________________________________________________-

 

[RuralGuy]

Try: Call ShowSubform and see what happens.

 

[fibayne]

Hi
just tried it and still get the runtime error 424, I found this is code from another forum it was in a sample DB and worked perfectly so I guess there must be something wrong within my Db to stop it working, I am searching on this runtime error to see if I can find anything that way..thanks again for your continued help and support.. fi
this is my code now just incase I have misunderstood your suggestion

If it helps the error if I select Online from the media type combo the coding error is where I have put the 2 stars this follows on in that if I select Press it goes to where I have put 3 stars, im sure this must be aclue to what is wrong

Sub CallShowSubform()

'Save unsaved changes to currently open subform
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'Display appropriate subform based on MediaType chosen
If MediaType = "Online" Then
** sf_VMPress.Visible = False
sf_VMDirectories.Visible = False
sf_VMOnline.Visible = True

ElseIf MediaType = "Press" Then
***sf_VMPress.Visible = True
sf_VMDirectories.Visible = False
sf_VMOnline.Visible = False

ElseIf MediaType = "Directories" Then
sf_VMPress.Visible = False
sf_VMDirectories.Visible = True
sf_VMOnline.Visible = False

Else
sf_VMOnline.Visible = False
sf_VMPress.Visible = False
sf_VMDirectories.Visible = False

End If

End Sub

Private Sub cmdClose_Click()

'Close form
DoCmd.close

End Sub

Private Sub Form_Current()

'Call subroutine to display appropriate subform based on MediaType
CallShowSubform

End Sub

Private Sub MediaType_AfterUpdate()

'Call subroutine to display appropriate subform based on MediaType
CallShowSubform

End Sub

 

[RuralGuy]

CallShowSubform needs to be 2 words! Call ShowSubform

 

[RuralGuy]

You Call SubRoutines. I didn't mean for you to rename your SubRoutine.

 

[fibayne]

Hi RG
hope I havent made you think i know what Im talking about during the past few conversations !!!

I changed the code to what I think I understand from your post but am getting 'Compile error Identifier expected' , can it be that the subform are not being recognised by how I have them set up, I have agian included the code so you can see any glowing errors

Option Compare Database

Sub Call ShowSubform()

'Save unsaved changes to currently open subform
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'Display appropriate subform based on MediaType chosen
If MediaType = "Online" Then
sf_VMPress.Visible = False
sf_VMDirectories.Visible = False
sf_VMOnline.Visible = True

ElseIf MediaType = "Press" Then
sf_VMPress.Visible = True
sf_VMDirectories.Visible = False
sf_VMOnline.Visible = False

ElseIf MediaType = "Directories" Then
sf_VMPress.Visible = False
sf_VMDirectories.Visible = True
sf_VMOnline.Visible = False

Else
sf_VMOnline.Visible = False
sf_VMPress.Visible = False
sf_VMDirectories.Visible = False

End If

End Sub

Private Sub cmdClose_Click()

'Close form
DoCmd.close

End Sub

Private Sub Form_Current()

'Call subroutine to display appropriate subform based on MediaType
Call ShowSubform

End Sub

Private Sub MediaType_AfterUpdate()

'Call subroutine to display appropriate subform based on MediaType
Call ShowSubform

End Sub

 

[fibayne]

Hi
Changed the codeslightly and it seems to be getting as far as
Private Sub Form Current () where it gives the error message 'Compile Error Sub or Function not defined' 4 * against the line below where the error seems to happen

thnaks again RG

Option Compare Database

Call Sub ShowSubform()

'Save unsaved changes to currently open subform
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'Display appropriate subform based on MediaType chosen
If MediaType = "Online" Then
sf_VMPress.Visible = False
sf_VMDirectories.Visible = False
sf_VMOnline.Visible = True

ElseIf MediaType = "Press" Then
sf_VMPress.Visible = True
sf_VMDirectories.Visible = False
sf_VMOnline.Visible = False

ElseIf MediaType = "Directories" Then
sf_VMPress.Visible = False
sf_VMDirectories.Visible = True
sf_VMOnline.Visible = False

Else
sf_VMOnline.Visible = False
sf_VMPress.Visible = False
sf_VMDirectories.Visible = False

End If

End Sub

Private Sub cmdClose_Click()

'Close form
DoCmd.close

End Sub

Private Sub Form_Current() ****

'Call subroutine to display appropriate subform based on MediaType
Call ShowSubform

End Sub

Private Sub MediaType_AfterUpdate()

'Call subroutine to display appropriate subform based on MediaType
Call ShowSubform

End Sub

 

[RuralGuy]

Change this line:
Sub Call ShowSubform()
to...
Sub ShowSubform()

 

[fibayne]

Hi RG
Sub ShowSubform() is what I had to start with, am I really not understanding what you are saying, does what I said on my post before this one have any meaning in that it seems to let me get further ??
Fi

 

[RuralGuy]

You keep changing it in at least two places! Stop doing that!
The SubRoutine is defined as:
Sub ShowSubform()
or
Private Sub ShowSubForm()
...and elsewhere when you want to invoke it you need:
Call ShowSubform

There are probably several problems but we'll deal with them one at a time.