Prevent Moving to New Record vs two Forms?

[Steve Stad]

I would like to prevent user from adding new record based on condition in a
field.
e.g., Is there a way to prevent moving to the 'add new record' line in a
form if field1 = "A" or "B" and still be able to EDIT fields for existing
records for A and B types in field1? I have been experimenting with nested
Elseif..if statements ... but thinking there may an easier way if I use two
forms based on different conditions.

 

[Marshall Barton]

I would like to prevent user from adding new record based on condition in a
field.
e.g., Is there a way to prevent moving to the 'add new record' line in a
form if field1 = "A" or "B" and still be able to EDIT fields for existing
records for A and B types in field1? I have been experimenting with nested
Elseif..if statements ... but thinking there may an easier way if I use two
forms based on different conditions.

You can get that effect by using code like this in the
form's Cuttent event:

Me.AllowAdditions = Not Me.field1 Like "[AB["

 

[Steve Stad]

Marshall - I tried this code in the form cuRRent event -- but got Run-time
error '94' Invalid use of null. I filled in all nulls in Field1 but still
got the same error msg.

Private Sub Form_Current()
Me.AllowAdditions = Not Me.Field1 Like "[A]"
End Sub

Can you explain the right side of the equal = Not Me.field1 Like "[AB["
I assume you meant to enclose AB w/a right bracket. I am testing with one
option, e.g., [A] ... as above Not Me.Field1 Like "[A]"

I would like to prevent user from adding new record based on condition in a
field.
e.g., Is there a way to prevent moving to the 'add new record' line in a
form if field1 = "A" or "B" and still be able to EDIT fields for existing
records for A and B types in field1? I have been experimenting with nested
Elseif..if statements ... but thinking there may an easier way if I use two
forms based on different conditions.

You can get that effect by using code like this in the
form's Cuttent event:

Me.AllowAdditions = Not Me.field1 Like "[AB["

 

[Marshall Barton]

Marshall - I tried this code in the form cuRRent event -- but got Run-time
error '94' Invalid use of null. I filled in all nulls in Field1 but still
got the same error msg.

Private Sub Form_Current()
Me.AllowAdditions = Not Me.Field1 Like "[A]"
End Sub

Can you explain the right side of the equal = Not Me.field1 Like "[AB["
I assume you meant to enclose AB w/a right bracket. I am testing with one
option, e.g., [A] ... as above Not Me.Field1 Like "[A]"

Not Me.field1 Like "[AB["
returns False if field1 is either "A" or "B" so additions
are Not allowed. Otherwise it returns True and additions
are allowed. Check Help on "Wildcards" for other things you
can do with the Like operator. (Note that testing with a
single letter inside the [ ] is the equivalent to using
Me.Field1 = "A")

You will get that error if Field1 is Null. If you want to
allow additions when Field1 is Null, try using:
Not Nz(Me.field1,"") Like "[AB["

 

[Steve Stad]

Marshall, Actuall A and B are really 3 characters "CIV" and "MIL".
So I need line or lines to to prevent new additions if me.field1 = CIV or
MIL and allow new newadditions if field1 is CTR.
I tried this code but got runtime error13 : type mismatch
Private Sub Form_Current()
Me.AllowAdditions = Not Nz(Me.field1, "")
Me.AllowAdditions = (Me.field1 = "CTR")
End Sub

sorry I did not specify to begin.

Marshall - I tried this code in the form cuRRent event -- but got Run-time
error '94' Invalid use of null. I filled in all nulls in Field1 but still
got the same error msg.

Private Sub Form_Current()
Me.AllowAdditions = Not Me.Field1 Like "[A]"
End Sub

Can you explain the right side of the equal = Not Me.field1 Like "[AB["
I assume you meant to enclose AB w/a right bracket. I am testing with one
option, e.g., [A] ... as above Not Me.Field1 Like "[A]"

Not Me.field1 Like "[AB["
returns False if field1 is either "A" or "B" so additions
are Not allowed. Otherwise it returns True and additions
are allowed. Check Help on "Wildcards" for other things you
can do with the Like operator. (Note that testing with a
single letter inside the [ ] is the equivalent to using
Me.Field1 = "A")

You will get that error if Field1 is Null. If you want to
allow additions when Field1 is Null, try using:
Not Nz(Me.field1,"") Like "[AB["

 

[Marshall Barton]

Marshall, Actuall A and B are really 3 characters "CIV" and "MIL".
So I need line or lines to to prevent new additions if me.field1 = CIV or
MIL and allow new newadditions if field1 is CTR.
I tried this code but got runtime error13 : type mismatch
Private Sub Form_Current()
Me.AllowAdditions = Not Nz(Me.field1, "")
Me.AllowAdditions = (Me.field1 = "CTR")
End Sub

sorry I did not specify to begin.

I wasted your time trying to use a one line solution. Like
is just not useful with the real data.

I think the code would then look like:

If Me.field1 = "CIV" Or Me.field1 = "MIL" Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If

 

[Steve Stad]

Thanks Barton - Can you tell me what event and how to move or set focus on
field1 when a user scrolls/rolls thru records in the form. I have alot of
code in the 'gotfocus' event for field1 so I want to ensure the focus moves
or starts in field1.
I am not sure if scrolling with mouse roller is opening, loading,
activating, etc???

 

[Marshall Barton]

There are no events when you just scroll through the
records. You have to **navigate** to a record to tell
Access which record is the current record.

I think you might want to use the form's Current event.

When the Current event occurs, you can set the focus to
whatever control you want. However, you do not need to move
the focus to run the code in the GotFocus event. You can
call it from anywhere just like any other procedure:
field1_GotFocus
or
Call field1_GotFocus()
--
Marsh
MVP [MS Access]

Thanks Barton - Can you tell me what event and how to move or set focus on
field1 when a user scrolls/rolls thru records in the form. I have alot of
code in the 'gotfocus' event for field1 so I want to ensure the focus moves
or starts in field1.
I am not sure if scrolling with mouse roller is opening, loading,
activating, etc???

Marshall, Actuall A and B are really 3 characters "CIV" and "MIL".
So I need line or lines to to prevent new additions if me.field1 = CIV or
MIL and allow new newadditions if field1 is CTR.

[snip]
I think the code would then look like:

If Me.field1 = "CIV" Or Me.field1 = "MIL" Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If

 

[Steve Stad]

Thanks Marshall - btw is the following code valid for Field1_GotFocus() for
an existing record, i.e., Not a new record. I have same code for
Field1_AfterUpdate() and. When I test I think one set of code is flipping
the enable/disable for the same fields. I need to enable certain fields for
editing existing and disable the same fields if it is a new record.

Private Sub Field1_GotFocus()
If Not Me.NewRecord Then
Me.[EMP_FIRST_NAME].Enabled = True
Me.[EMP_CITY].Enabled = True
Me.[EMP_STATE].Enabled = True
If Me.[ Field1] = "MIL" Then
Me.[EMP_FIRST_NAME].Enabled = False
Me.[EMP_CITY].Enabled = False
Me.[EMP_STATE].Enabled = False
End If
If Me.[ Field1] = "CIV" Then
Me.[EMP_FIRST_NAME].Enabled = False
Me.[EMP_CITY].Enabled = False
Me.[EMP_STATE].Enabled = False
End If
Else
Me.[EMP_FIRST_NAME].Enabled = True
Me.[EMP_CITY].Enabled = True
Me.[EMP_STATE].Enabled = True
End If
End Sub

There are no events when you just scroll through the
records. You have to **navigate** to a record to tell
Access which record is the current record.

I think you might want to use the form's Current event.

When the Current event occurs, you can set the focus to
whatever control you want. However, you do not need to move
the focus to run the code in the GotFocus event. You can
call it from anywhere just like any other procedure:
field1_GotFocus
or
Call field1_GotFocus()
--
Marsh
MVP [MS Access]

Thanks Barton - Can you tell me what event and how to move or set focus on
field1 when a user scrolls/rolls thru records in the form. I have alot of
code in the 'gotfocus' event for field1 so I want to ensure the focus moves
or starts in field1.
I am not sure if scrolling with mouse roller is opening, loading,
activating, etc???

Steve Stad wrote:
Marshall, Actuall A and B are really 3 characters "CIV" and "MIL".
So I need line or lines to to prevent new additions if me.field1 = CIV or
MIL and allow new newadditions if field1 is CTR. [snip]
I think the code would then look like:

If Me.field1 = "CIV" Or Me.field1 = "MIL" Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If

.

 

[Marshall Barton]

Thanks Marshall - btw is the following code valid for Field1_GotFocus() for
an existing record, i.e., Not a new record. I have same code for
Field1_AfterUpdate() and. When I test I think one set of code is flipping
the enable/disable for the same fields. I need to enable certain fields for
editing existing and disable the same fields if it is a new record.

Private Sub Field1_GotFocus()
If Not Me.NewRecord Then
Me.[EMP_FIRST_NAME].Enabled = True
Me.[EMP_CITY].Enabled = True
Me.[EMP_STATE].Enabled = True
If Me.[ Field1] = "MIL" Then
Me.[EMP_FIRST_NAME].Enabled = False
Me.[EMP_CITY].Enabled = False
Me.[EMP_STATE].Enabled = False
End If
If Me.[ Field1] = "CIV" Then
Me.[EMP_FIRST_NAME].Enabled = False
Me.[EMP_CITY].Enabled = False
Me.[EMP_STATE].Enabled = False
End If
Else
Me.[EMP_FIRST_NAME].Enabled = True
Me.[EMP_CITY].Enabled = True
Me.[EMP_STATE].Enabled = True
End If
End Sub

That may be valid, but I find it confusingly difficult to
read. I think this may be easier to follow:

Private Sub Field1_GotFocus()
If Not Me.NewRecord Then
If Me.[ Field1] = "MIL" Or Me.[ Field1] = "CIV" Then
Me.[EMP_FIRST_NAME].Enabled = False
Me.[EMP_CITY].Enabled = False
Me.[EMP_STATE].Enabled = False
Else
Me.[EMP_FIRST_NAME].Enabled = True
Me.[EMP_CITY].Enabled = True
Me.[EMP_STATE].Enabled = True
End If
Else
Me.[EMP_FIRST_NAME].Enabled = True
Me.[EMP_CITY].Enabled = True
Me.[EMP_STATE].Enabled = True
End If
End Sub

BUT, I think that code should be in the form's Current
event, not in any control's GotFocus event. Maybe I just
don't understand the significance of [ Field1]??

I really have to question the name [ Field1] with a space as
the first character. It should work, but it is extremely
unusual and prone to typos.