Altering a combo list (1 Viewer)

ECEK · Sep 25, 2017

Could anybody point me in the right direction:

field1 Ongotfocus if field2 = "List" Then

Use just one option "Letter"
Else
Use the data that is already being used to populate the combobox

I have tried to say what I'm trying to do.

Hope you can help ?

isladogs · Sep 25, 2017

In theory this might work:

Code:

Private Sub Field1_GotFocus()

	If Me.Field2="List" Then
                Me.ComboBox.RowSourceType = "Valuelist"
                Me.ComboBox.RowSource = "Letter"
	Else
	   'your normal row source code goes here				
	End If

End Sub

However as soon as you move away from field1, it loses focus so the combo box row source will revert to normal.
So if you want to do this I think you need to use another event like on click or lost focus for Field1

ECEK · Sep 25, 2017

I got this error:
The record source "Letter" specified on this form does not exist.
The name of the record source may be misspelled.

ECEK · Sep 25, 2017

I don't know what my normal code is. I use the properties to create the "usual" data list

isladogs · Sep 25, 2017

Sorry it should have said "Value list"

Code:

Private Sub Field1_GotFocus()

	If Me.Field2="List" Then
                Me.ComboBox.RowSourceType = "[B][COLOR="DarkRed"]Value list[/COLOR][/B]"
                Me.ComboBox.RowSource = "Letter"
	Else
	   'your normal row source code goes here				
	End If

End Sub

However, you may have noticed that I edited my original post - have a look at the 2nd part

ECEK · Sep 26, 2017

Ridders:
This works perfectly however I am struggling with the ELSE code
At present the current list is a query based on criteria in another field.

How do I either Nullify your code or replicate it in VBA?

I have tried to copy the text that is in the properties Row Source like so, but there is a syntax error.

Code:

SELECT tbl_dd_rev_type.PWRTEXT, tbl_user.consultants AS admin_name, tbl_dd_rev_type.rev_type FROM tbl_dd_rev_type, tbl_user WHERE (((tbl_dd_rev_type.rev_type)=[Forms]![frm_single].[tor]));

Could you suggest a solution?

ECEK · Sep 26, 2017

Quick Update:
It appears to work !!
I think there may have been a save issue with the form.
I'll try it out then report back

isladogs · Sep 26, 2017

Good though I'm surprised it does.
Check it also reverts to the default row source after something else gets focus.

BTW I suggest you change your field and table names to remove the repeated use of underscores or spaces. Also don't use full stops in field/table names

Doing this would make your code easier to read /write and less prone to errors

ECEK · Sep 26, 2017

Hi Ridders
You were right. It works fine but only once !!
I do need something ELSE
The proceedure works but if I go and change it to something else then the edited list still remains.
I need ELSE to either nullify (or refresh back to the forms original state) and go back to my original list data or be able to write my original list as code.

BTW Thanks for the heads up on naming my tables I thought that if there was atleast something in there then its OK.

ECEK · Sep 26, 2017

Oh BTW I didn't use the original code in the ELSE.
It's just left blank (No ELSE)

Altering a combo list (1 Viewer)

ECEK

Registered User.

isladogs

MVP / VIP

ECEK

Registered User.

ECEK

Registered User.

isladogs

MVP / VIP

ECEK

Registered User.

ECEK

Registered User.

isladogs

MVP / VIP

ECEK

Registered User.

ECEK

Registered User.

Similar threads

Users who are viewing this thread