Prev: Frustrating Backcolor issue
Next: Change Field BackColours When A Tickbox Is True
From: Maurice on 29 Mar 2010 08:39
You've set up the tables according to your wish to use ID's instead of
strings well done.
Now don't get caught in setting up the table twice. In the example you
describe below i see two tables which hold the same data that shouldn't be
the case and is redundant.

Let's go here:

Your form has a combo called: Go_to_Pstn02. The source you've set is correct
but at the id as well like this:

SELECT PositionRef.[ID], PositionRef.[Position]
FROM PositionRef
ORDER BY PositionRef.[Position]

set the value of the columnwidth of the combo to 0", 2"
This will hide the column id but it will be used for reference. Set column 1
to be the bound column.

Now in the after update of the combo set the following piece:

Private Sub Go_to_Pstn02_AfterUpdate()

Dim strFormName As String
strFormName = DLookup("PositionForm_", "PositionRef", "ID=" &
Me.[Go_to_Pstn02])

DoCmd.OpenForm strFormName

End Sub

I used the table [PositionRef] to retrieve your formname. That's the only
one you need. Drop the table which i suggested because that's redundant now,
also remove the table FormNameRef because that's also redundant now.

The flow is as follows: in the source of the combo the first column is the
id column which is not visible because it's set to columnwidth 0(zero). When
a selection is made the after update fires. The after update event does a
lookup where it takes the ID from the combobox and looks this up in the table
[PositionRef]. Suppose the id =1 then the Dlookup will return the formname
"FormApplicationPg02Cstdn" and place this in the variable "strFormName".
After that the docmd. event triggers the form to open the form:
FormApplicationPg02Cstdn.

If you change the name of the form all you have to do is change the name of
the form in the table PositionRef and the after update event will still work
as expected. If you want to add a form also add the name of the report to
this table and the after update will remain working as defined before.

hth



--
Maurice Ausum


 | 
Pages: 1
Prev: Frustrating Backcolor issue
Next: Change Field BackColours When A Tickbox Is True