Opening one form and then another based on value in the first

[prmitchell]

Hi
I have two tables where one Client has many jobs - and I have a switchboard with a combo box showing the existing clients where the user selects a client and then opens a Jobs form to enter a new job (Add mode) and so the user types in the details of the job but naturally I want the job form to record the clientID (in the Jobs table).
So after the user enters a job name (first field in the jobs form) I have an event on AfterUpdate which is
[ClientID].Value = Forms!Menu![ClientID].Value
Forms![Jobs].Refresh
DoCmd.Save acForm, "jobs"

This works fine for me using 2010 - every time - but I give to a user who is using 2007 - and it doesn't work every time.

Do I need all three lines, do I need something else, doe I need something different? Can you do this for when the form opens rather than when the user types in a job name (AfterUpdate)?


alternatively I have tried using the where condition on opening a form, namely
"[ClientID]=" & "'" & [ClientID] & "'"
but this doesn't work for add data mode, or does it somehow?

Peter

 

[Pat Hartman]

I don't know why A2007 is acting differently from A2010. Have the client make sure that his A2007 is patched.

A better event to use is the form's BeforeInsert event. This event runs once per form immediately after the first character is typed in ANY control so you don't have to rely on the user following your anticipated data entry sequence. The syntax is

Me.ClientID = Forms!Menu!ClientID

Always use "Me." when referring to a control on the current form. It eliminates the need for Access to search all loaded libraries to determine where the ClientID is defined. "Me" tells it. The .value property is not required since it is the default property for controls. Use it if you like typing. Although, if you use "Me.", intellisense will save you most of the typing.

 

[prmitchell]

Thank you Pat, much appreciated.
I have also found in addition to your advice is to also include the SaveRecord method on the button on the first form that activates the second form opening.

Touch-wood, all working now.