> I have a strange situation in which a New Record is called up in certain
> situations, but does not accept any new data.  The New Record is called by
> clicking on a Command Button on a form.  If it is clicked right after you
> have entered one record using that form ('FCycDrugAdminAmplimex'), and you
> want to enter another, that works fine.  What doesn't work is when one
> goes
> back into an existing record using this form ('FCycDrugAdminAmplimex'),
> and
> then clicks on the 'New Record' Command Button.  The new form opens, and
> 'stepping thru' the code, it clearly calls a New Record, fills out the
> Header
> field info. executing 'SetAutoValues(Me)', and clearly does NOT call the
> sub
> to lock the controls on the form LockControls(Me).  I am baffled, but the
> blank form in this situation is behaving as if it is 'Locked', it is as if
> it
> is 'inheriting' the Lock Records charactieristic that all records that are
> input into my database have after entry.  I have ensure that I do NOT have
> 'Record Locks on the Form Property set to 'Yes', nor are any of the fields
> 'Locked' Property set to 'Yes'.  What am I missing here?

> Here is the code behind the Command Button:

> Private Sub CommandOpenNewRecord_Click()
> DoCmd.OpenForm "FCycDrugAdminAmplimex", acNormal
> DoCmd.GoToRecord acDataForm, "FCycDrugAdminAmplimex", acNewRec
> DoCmd.GoToControl "cyclenum"
> End Sub

> Once it hits 'acNewRec', then this code is called on the Form's OnCurrent
> Event:

> Private Sub Form_Current()
> If Me.NewRecord Then
> Call SetAutoValues(Me)
> Else
> Call LockControls(Me)
> End If
> End Sub

> Sub SetAutoValues(frm As Form)
> On Error GoTo SetAutoValues_err

>    ' Set Automatic Values in each form in series
>    ' Add as many fields as necessary (make sure each field has the same
> name on EVERY form)
>    With frm
>        !id = Forms!fEnterPatientInfo!id
>        !ptin = Forms!fEnterPatientInfo!ptin
>        !site = Forms!fEnterPatientInfo!site
>    End With

> SetAutoValues_err:
>    'MsgBox Err.Description
>    Resume Next

> End Sub

> Thank you.
> --
> Pat Dools

> Your code is locking the controls when it displays an existing record:
> > Else
> > Call LockControls(Me)
> but it is not unlocking then when it subsequently displays a new record.

> I guess your LockControls procedure does something like this:

> Sub LockControls( f as Form)
> Dim ctl as control
> For each ctl in f
>     If .... then
>         ctl.Locked = true
>     End If
> Next ctl
> End Sub

> I suggest you modify it so it can be used both to lock AND unlock the
> controls:

> Sub LockControls( f as form, LockValue as boolean)
> ....
>         ctl.Locked = LockValue
> ....

> Then your Form_Current procedure can unlock the controls for a new record:
>     If Me.NewRecord Then
>         Call SetAutoValues(Me)
>         Call LockControls(Me, False)
>     Else
>         Call LockControls(Me, True)
>     End If
> --
> Good Luck!

> Graham Mandeno [Access MVP]
> Auckland, New Zealand

> > Hello,

> > I have a strange situation in which a New Record is called up in certain
> > situations, but does not accept any new data.  The New Record is called by
> > clicking on a Command Button on a form.  If it is clicked right after you
> > have entered one record using that form ('FCycDrugAdminAmplimex'), and you
> > want to enter another, that works fine.  What doesn't work is when one
> > goes
> > back into an existing record using this form ('FCycDrugAdminAmplimex'),
> > and
> > then clicks on the 'New Record' Command Button.  The new form opens, and
> > 'stepping thru' the code, it clearly calls a New Record, fills out the
> > Header
> > field info. executing 'SetAutoValues(Me)', and clearly does NOT call the
> > sub
> > to lock the controls on the form LockControls(Me).  I am baffled, but the
> > blank form in this situation is behaving as if it is 'Locked', it is as if
> > it
> > is 'inheriting' the Lock Records charactieristic that all records that are
> > input into my database have after entry.  I have ensure that I do NOT have
> > 'Record Locks on the Form Property set to 'Yes', nor are any of the fields
> > 'Locked' Property set to 'Yes'.  What am I missing here?

> > Here is the code behind the Command Button:

> > Private Sub CommandOpenNewRecord_Click()
> > DoCmd.OpenForm "FCycDrugAdminAmplimex", acNormal
> > DoCmd.GoToRecord acDataForm, "FCycDrugAdminAmplimex", acNewRec
> > DoCmd.GoToControl "cyclenum"
> > End Sub

> > Once it hits 'acNewRec', then this code is called on the Form's OnCurrent
> > Event:

> > Private Sub Form_Current()
> > If Me.NewRecord Then
> > Call SetAutoValues(Me)
> > Else
> > Call LockControls(Me)
> > End If
> > End Sub

> > Sub SetAutoValues(frm As Form)
> > On Error GoTo SetAutoValues_err

> >    ' Set Automatic Values in each form in series
> >    ' Add as many fields as necessary (make sure each field has the same
> > name on EVERY form)
> >    With frm
> >        !id = Forms!fEnterPatientInfo!id
> >        !ptin = Forms!fEnterPatientInfo!ptin
> >        !site = Forms!fEnterPatientInfo!site
> >    End With

> > SetAutoValues_err:
> >    'MsgBox Err.Description
> >    Resume Next

> > End Sub

> > Thank you.
> > --
> > Pat Dools

> That works great and thank you!  Not to ask too much, but would you mind
> offering any insight on another problem that's been tough to solve?  I
> have
> some code behind a Command Button that I would like to execute on the
> Form's
> Before Update Event to check to see if there is a duplicate value for the
> primary key (which consists of 3 fields in my example, all of which are
> Indexed and 'Duplicate OK' on the individual field level).  I would like a
> custom MsgBox to let the user know that they have attempted to enter a
> duplicate Key combination.  Right now, the form simply closes and does NOT
> save the record to the table (as it shouldn't), but the user has no
> message
> alerting him besides 'you can't go to the specified record'.  I am
> attempting
> to use 'DCount' in the following code sample:

> Private Sub Form_BeforeUpdate(Cancel As Integer)
> Dim lngKey As Integer
> Dim IngKey2 As Integer
> Dim IngKey3 As Integer
> If DCount("[id]", "tCycTumorMarker", "(id = " & lngKey & ") AND
> (tmarker_test_number = " & IngKey2 & ")AND (cyclenum = " & IngKey3 & ")")
>  > 0
> Then
> Cancel = True
> MsgBox "Key value is already in the Table"
> End If
> End Sub

> When I step thru the code (after entering a 'test record' w/ a duplicate
> Key), it evaluates the 'If...' and skips over 'Cancel=True' and the
> MsgBox,
> so I assume the value is not greater than 0?  I'm not terribly familiar w/
> VBA syntax requirements, but the above code does compile.  Any thoughts
> you
> might have would be greatly appreciated, and thank you again for your help
> w/
> my original problem!

> Thanks.

>> Hi Pat

>> Your code is locking the controls when it displays an existing record:
>> > Else
>> > Call LockControls(Me)
>> but it is not unlocking then when it subsequently displays a new record.

>> I guess your LockControls procedure does something like this:

>> Sub LockControls( f as Form)
>> Dim ctl as control
>> For each ctl in f
>>     If .... then
>>         ctl.Locked = true
>>     End If
>> Next ctl
>> End Sub

>> I suggest you modify it so it can be used both to lock AND unlock the
>> controls:

>> Sub LockControls( f as form, LockValue as boolean)
>> ....
>>         ctl.Locked = LockValue
>> ....

>> Then your Form_Current procedure can unlock the controls for a new
>> record:
>>     If Me.NewRecord Then
>>         Call SetAutoValues(Me)
>>         Call LockControls(Me, False)
>>     Else
>>         Call LockControls(Me, True)
>>     End If
>> --
>> Good Luck!

>> Graham Mandeno [Access MVP]
>> Auckland, New Zealand

>> > Hello,

>> > I have a strange situation in which a New Record is called up in
>> > certain
>> > situations, but does not accept any new data.  The New Record is called
>> > by
>> > clicking on a Command Button on a form.  If it is clicked right after
>> > you
>> > have entered one record using that form ('FCycDrugAdminAmplimex'), and
>> > you
>> > want to enter another, that works fine.  What doesn't work is when one
>> > goes
>> > back into an existing record using this form ('FCycDrugAdminAmplimex'),
>> > and
>> > then clicks on the 'New Record' Command Button.  The new form opens,
>> > and
>> > 'stepping thru' the code, it clearly calls a New Record, fills out the
>> > Header
>> > field info. executing 'SetAutoValues(Me)', and clearly does NOT call
>> > the
>> > sub
>> > to lock the controls on the form LockControls(Me).  I am baffled, but
>> > the
>> > blank form in this situation is behaving as if it is 'Locked', it is as
>> > if
>> > it
>> > is 'inheriting' the Lock Records charactieristic that all records that
>> > are
>> > input into my database have after entry.  I have ensure that I do NOT
>> > have
>> > 'Record Locks on the Form Property set to 'Yes', nor are any of the
>> > fields
>> > 'Locked' Property set to 'Yes'.  What am I missing here?

>> > Here is the code behind the Command Button:

>> > Private Sub CommandOpenNewRecord_Click()
>> > DoCmd.OpenForm "FCycDrugAdminAmplimex", acNormal
>> > DoCmd.GoToRecord acDataForm, "FCycDrugAdminAmplimex", acNewRec
>> > DoCmd.GoToControl "cyclenum"
>> > End Sub

>> > Once it hits 'acNewRec', then this code is called on the Form's
>> > OnCurrent
>> > Event:

>> > Private Sub Form_Current()
>> > If Me.NewRecord Then
>> > Call SetAutoValues(Me)
>> > Else
>> > Call LockControls(Me)
>> > End If
>> > End Sub

>> > Sub SetAutoValues(frm As Form)
>> > On Error GoTo SetAutoValues_err

>> >    ' Set Automatic Values in each form in series
>> >    ' Add as many fields as necessary (make sure each field has the same
>> > name on EVERY form)
>> >    With frm
>> >        !id = Forms!fEnterPatientInfo!id
>> >        !ptin = Forms!fEnterPatientInfo!ptin
>> >        !site = Forms!fEnterPatientInfo!site
>> >    End With

>> > SetAutoValues_err:
>> >    'MsgBox Err.Description
>> >    Resume Next

>> > End Sub

>> > Thank you.
>> > --
>> > Pat Dools

> Your code is locking the controls when it displays an existing record:
> > Else
> > Call LockControls(Me)
> but it is not unlocking then when it subsequently displays a new record.

> I guess your LockControls procedure does something like this:

> Sub LockControls( f as Form)
> Dim ctl as control
> For each ctl in f
>     If .... then
>         ctl.Locked = true
>     End If
> Next ctl
> End Sub

> I suggest you modify it so it can be used both to lock AND unlock the
> controls:

> Sub LockControls( f as form, LockValue as boolean)
> ....
>         ctl.Locked = LockValue
> ....

> Then your Form_Current procedure can unlock the controls for a new record:
>     If Me.NewRecord Then
>         Call SetAutoValues(Me)
>         Call LockControls(Me, False)
>     Else
>         Call LockControls(Me, True)
>     End If
> --
> Good Luck!

> Graham Mandeno [Access MVP]
> Auckland, New Zealand

> > Hello,

> > I have a strange situation in which a New Record is called up in certain
> > situations, but does not accept any new data.  The New Record is called by
> > clicking on a Command Button on a form.  If it is clicked right after you
> > have entered one record using that form ('FCycDrugAdminAmplimex'), and you
> > want to enter another, that works fine.  What doesn't work is when one
> > goes
> > back into an existing record using this form ('FCycDrugAdminAmplimex'),
> > and
> > then clicks on the 'New Record' Command Button.  The new form opens, and
> > 'stepping thru' the code, it clearly calls a New Record, fills out the
> > Header
> > field info. executing 'SetAutoValues(Me)', and clearly does NOT call the
> > sub
> > to lock the controls on the form LockControls(Me).  I am baffled, but the
> > blank form in this situation is behaving as if it is 'Locked', it is as if
> > it
> > is 'inheriting' the Lock Records charactieristic that all records that are
> > input into my database have after entry.  I have ensure that I do NOT have
> > 'Record Locks on the Form Property set to 'Yes', nor are any of the fields
> > 'Locked' Property set to 'Yes'.  What am I missing here?

> > Here is the code behind the Command Button:

> > Private Sub CommandOpenNewRecord_Click()
> > DoCmd.OpenForm "FCycDrugAdminAmplimex", acNormal
> > DoCmd.GoToRecord acDataForm, "FCycDrugAdminAmplimex", acNewRec
> > DoCmd.GoToControl "cyclenum"
> > End Sub

> > Once it hits 'acNewRec', then this code is called on the Form's OnCurrent
> > Event:

> > Private Sub Form_Current()
> > If Me.NewRecord Then
> > Call SetAutoValues(Me)
> > Else
> > Call LockControls(Me)
> > End If
> > End Sub

> > Sub SetAutoValues(frm As Form)
> > On Error GoTo SetAutoValues_err

> >    ' Set Automatic Values in each form in series
> >    ' Add as many fields as necessary (make sure each field has the same
> > name on EVERY form)
> >    With frm
> >        !id = Forms!fEnterPatientInfo!id
> >        !ptin = Forms!fEnterPatientInfo!ptin
> >        !site = Forms!fEnterPatientInfo!site
> >    End With

> > SetAutoValues_err:
> >    'MsgBox Err.Description
> >    Resume Next

> > End Sub

> > Thank you.
> > --
> > Pat Dools

> Just a quick question about using the 'Tag' property of a field to prevent
> locking of specific controls.  I need a specific ComboBox on a specific
> Form
> to NOT lock, even if changed on an already-entered (and locked) record.  I
> adjusted the code to secure the form to this:

> Public Sub LockControls(frm As Form, LockValue As Boolean)
> On Error Resume Next
> Dim ctl As Control

>    For Each ctl In frm.Controls
>        With ctl

>            Select Case .ControlType
>                Case acTextBox
>                    ctl.Locked = LockValue

>                Case acComboBox
>                    If ctl.Tag = 2 Then
>                    ctl.Enabled = True
>                    Else: ctl.Locked = LockValue
>                    End If

> It seems to work, but the only thing I'm concerned about it in the 'Case
> acComboBox' case, the 'Else' line has a colon (:)?  It just 'appeared'
> once I
> completed typing the line.  The code compiles correctly, and it appears to
> 'lock' Combo Boxes that do not have a 'Tag' value of '2' (while NOT
> locking
> those that do have a value of '2'), but I was curious about the colon that
> is
> being thrown in.  Is this anything to worry about causing trouble down the
> road?  Its just a bit unfamiliar, that's all.

> Thx, Patrick

>> Hi Pat

>> Your code is locking the controls when it displays an existing record:
>> > Else
>> > Call LockControls(Me)
>> but it is not unlocking then when it subsequently displays a new record.

>> I guess your LockControls procedure does something like this:

>> Sub LockControls( f as Form)
>> Dim ctl as control
>> For each ctl in f
>>     If .... then
>>         ctl.Locked = true
>>     End If
>> Next ctl
>> End Sub

>> I suggest you modify it so it can be used both to lock AND unlock the
>> controls:

>> Sub LockControls( f as form, LockValue as boolean)
>> ....
>>         ctl.Locked = LockValue
>> ....

>> Then your Form_Current procedure can unlock the controls for a new
>> record:
>>     If Me.NewRecord Then
>>         Call SetAutoValues(Me)
>>         Call LockControls(Me, False)
>>     Else
>>         Call LockControls(Me, True)
>>     End If
>> --
>> Good Luck!

>> Graham Mandeno [Access MVP]
>> Auckland, New Zealand

>> > Hello,

>> > I have a strange situation in which a New Record is called up in
>> > certain
>> > situations, but does not accept any new data.  The New Record is called
>> > by
>> > clicking on a Command Button on a form.  If it is clicked right after
>> > you
>> > have entered one record using that form ('FCycDrugAdminAmplimex'), and
>> > you
>> > want to enter another, that works fine.  What doesn't work is when one
>> > goes
>> > back into an existing record using this form ('FCycDrugAdminAmplimex'),
>> > and
>> > then clicks on the 'New Record' Command Button.  The new form opens,
>> > and
>> > 'stepping thru' the code, it clearly calls a New Record, fills out the
>> > Header
>> > field info. executing 'SetAutoValues(Me)', and clearly does NOT call
>> > the
>> > sub
>> > to lock the controls on the form LockControls(Me).  I am baffled, but
>> > the
>> > blank form in this situation is behaving as if it is 'Locked', it is as
>> > if
>> > it
>> > is 'inheriting' the Lock Records charactieristic that all records that
>> > are
>> > input into my database have after entry.  I have ensure that I do NOT
>> > have
>> > 'Record Locks on the Form Property set to 'Yes', nor are any of the
>> > fields
>> > 'Locked' Property set to 'Yes'.  What am I missing here?

>> > Here is the code behind the Command Button:

>> > Private Sub CommandOpenNewRecord_Click()
>> > DoCmd.OpenForm "FCycDrugAdminAmplimex", acNormal
>> > DoCmd.GoToRecord acDataForm, "FCycDrugAdminAmplimex", acNewRec
>> > DoCmd.GoToControl "cyclenum"
>> > End Sub

>> > Once it hits 'acNewRec', then this code is called on the Form's
>> > OnCurrent
>> > Event:

>> > Private Sub Form_Current()
>> > If Me.NewRecord Then
>> > Call SetAutoValues(Me)
>> > Else
>> > Call LockControls(Me)
>> > End If
>> > End Sub

>> > Sub SetAutoValues(frm As Form)
>> > On Error GoTo SetAutoValues_err

>> >    ' Set Automatic Values in each form in series
>> >    ' Add as many fields as necessary (make sure each field has the same
>> > name on EVERY form)
>> >    With frm
>> >        !id = Forms!fEnterPatientInfo!id
>> >        !ptin = Forms!fEnterPatientInfo!ptin
>> >        !site = Forms!fEnterPatientInfo!site
>> >    End With

>> > SetAutoValues_err:
>> >    'MsgBox Err.Description
>> >    Resume Next

>> > End Sub

>> > Thank you.
>> > --
>> > Pat Dools

> There are two forms for the If... statement: one-line or multiline.

> In the one-line form, the whole lot should be on one line:
>     If Tag = 2 Then ctl.Enabled = True Else ctl.Locked = LockValue

> In the multiline form you can have blocks of one or more statements in both
> the Then and the Else sections, so the multiline equivalent is:
>     If Tag = 2 Then
>         ctl.Enabled = True
>     Else
>         ctl.Locked = LockValue
>     End If

> This is more versatile (because you can put multiple statements in each
> block) and easier to read.

> You attempted to use a hybrid of the two:
>     If Tag = 2 Then
>         ctl.Enabled = True
>     Else ctl.Locked = LockValue
>     End If

> This is not correct syntax, so VBA (helpfully?) inserted a line separator
> character (:) after the Else.
> --
> Good Luck!

> Graham Mandeno [Access MVP]
> Auckland, New Zealand

> > Hi Graham,

> > Just a quick question about using the 'Tag' property of a field to prevent
> > locking of specific controls.  I need a specific ComboBox on a specific
> > Form
> > to NOT lock, even if changed on an already-entered (and locked) record.  I
> > adjusted the code to secure the form to this:

> > Public Sub LockControls(frm As Form, LockValue As Boolean)
> > On Error Resume Next
> > Dim ctl As Control

> >    For Each ctl In frm.Controls
> >        With ctl

> >            Select Case .ControlType
> >                Case acTextBox
> >                    ctl.Locked = LockValue

> >                Case acComboBox
> >                    If ctl.Tag = 2 Then
> >                    ctl.Enabled = True
> >                    Else: ctl.Locked = LockValue
> >                    End If

> > It seems to work, but the only thing I'm concerned about it in the 'Case
> > acComboBox' case, the 'Else' line has a colon (:)?  It just 'appeared'
> > once I
> > completed typing the line.  The code compiles correctly, and it appears to
> > 'lock' Combo Boxes that do not have a 'Tag' value of '2' (while NOT
> > locking
> > those that do have a value of '2'), but I was curious about the colon that
> > is
> > being thrown in.  Is this anything to worry about causing trouble down the
> > road?  Its just a bit unfamiliar, that's all.

> > Thx, Patrick

> >> Hi Pat

> >> Your code is locking the controls when it displays an existing record:
> >> > Else
> >> > Call LockControls(Me)
> >> but it is not unlocking then when it subsequently displays a new record.

> >> I guess your LockControls procedure does something like this:

> >> Sub LockControls( f as Form)
> >> Dim ctl as control
> >> For each ctl in f
> >>     If .... then
> >>         ctl.Locked = true
> >>     End If
> >> Next ctl
> >> End Sub

> >> I suggest you modify it so it can be used both to lock AND unlock the
> >> controls:

> >> Sub LockControls( f as form, LockValue as boolean)
> >> ....
> >>         ctl.Locked = LockValue
> >> ....

> >> Then your Form_Current procedure can unlock the controls for a new
> >> record:
> >>     If Me.NewRecord Then
> >>         Call SetAutoValues(Me)
> >>         Call LockControls(Me, False)
> >>     Else
> >>         Call LockControls(Me, True)
> >>     End If
> >> --
> >> Good Luck!

> >> Graham Mandeno [Access MVP]
> >> Auckland, New Zealand

> >> > Hello,

> >> > I have a strange situation in which a New Record is called up in
> >> > certain
> >> > situations, but does not accept any new data.  The New Record is called
> >> > by
> >> > clicking on a Command Button on a form.  If it is clicked right after
> >> > you
> >> > have entered one record using that form ('FCycDrugAdminAmplimex'), and
> >> > you
> >> > want to enter another, that works fine.  What doesn't work is when one
> >> > goes
> >> > back into an existing record using this form ('FCycDrugAdminAmplimex'),
> >> > and
> >> > then clicks on the 'New Record' Command Button.  The new form opens,
> >> > and
> >> > 'stepping thru' the code, it clearly calls a New Record, fills out the
> >> > Header
> >> > field info. executing 'SetAutoValues(Me)', and clearly does NOT call
> >> > the
> >> > sub
> >> > to lock the controls on the form LockControls(Me).  I am baffled, but
> >> > the
> >> > blank form in this situation is behaving as if it is 'Locked', it is as
> >> > if
> >> > it
> >> > is 'inheriting' the Lock Records charactieristic that all records that
> >> > are
> >> > input into my database have after entry.  I have ensure that I do NOT
> >> > have
> >> > 'Record Locks on the Form Property set to 'Yes', nor are any of the
> >> > fields
> >> > 'Locked' Property set to 'Yes'.  What am I missing here?

> >> > Here is the code behind the Command Button:

> >> > Private Sub CommandOpenNewRecord_Click()
> >> > DoCmd.OpenForm "FCycDrugAdminAmplimex", acNormal
> >> > DoCmd.GoToRecord acDataForm, "FCycDrugAdminAmplimex", acNewRec
> >> > DoCmd.GoToControl "cyclenum"
> >> > End Sub

> >> > Once it hits 'acNewRec', then this code is called on the Form's
> >> > OnCurrent
> >> > Event:

> >> > Private Sub Form_Current()
> >> > If Me.NewRecord Then
> >> > Call SetAutoValues(Me)
> >> > Else
> >> > Call LockControls(Me)
> >> > End If
> >> > End Sub

> >> > Sub SetAutoValues(frm As Form)
> >> > On Error GoTo SetAutoValues_err

> >> >    ' Set Automatic Values in each form in series
> >> >    ' Add as many fields as necessary (make sure each field has the same
> >> > name on EVERY form)
> >> >    With frm
> >> >        !id = Forms!fEnterPatientInfo!id
> >> >        !ptin = Forms!fEnterPatientInfo!ptin
> >> >        !site = Forms!fEnterPatientInfo!site
> >> >    End With

> >> > SetAutoValues_err:
> >> >    'MsgBox Err.Description
> >> >    Resume Next

> >> > End Sub

> >> > Thank you.
> >> > --
> >> > Pat Dools