Automatic Fill for Certain Conditions....

  • Thread starter Thread starter GoBrowns!
  • Start date Start date
G

GoBrowns!

Not even sure if this is possible, but here goes.

I have a form that allows users to input a "reason code" for each line of
data. Is there a way to:

Have the form automatically code items that have a percentage delivery
higher than 95%?

Have the form automatically code items that are blank in the field
"remaining" with the value that appeared in the "required" field?

Please help - if it is indeed possible!

Let me know if any other information is needed.

Thanks!
 
GoBrowns! said:
Not even sure if this is possible, but here goes.

I have a form that allows users to input a "reason code" for each line of
data. Is there a way to:

Have the form automatically code items that have a percentage delivery
higher than 95%?

Have the form automatically code items that are blank in the field
"remaining" with the value that appeared in the "required" field?

Please help - if it is indeed possible!

Let me know if any other information is needed.
Click to expand...

In the form's BeforeUpdate event use something like (untested):

Private Sub Form_BeforeUpdate (Cancel As Integer)
If Len(Me.txtReasonCode & vbNullString) = 0 Then
If Me.txtPercentageDelivery > than .95 Then
Me.txtReasonCode = "Whatever"
End If
End If

If Len(Me.txtRemaining & vbNullString) = 0 Then
Me.txtRemaining = Me.txtRequired
End If
End Sub
 
I am still having trouble. I tried putting the following in both the Before
Update and On Open events... thinking that I needed these fields to already
be filled in when the user opens the form....

Private Sub Form_Open(Cancel As Integer)
If Len(Me.Reason_Code & vbNullString) = 0 Then
If Me.Percentage_Complete > 0.95 Then
Me.Reason_Code = "CNF"
End If
End If
End Sub

I get an error that I cannot assign a value to this object. Can you please
help?

The second part of the code you gave me.... I am assuming that is to fill in
my "Remaining" field and not related to allowing me to auto-populate a reason
code.

Thanks so much for all your help!!!!!!!!!!
 
That did not work either.

When I put both pieces of code in, I get the error:

"Compile error: Argument not optional"

and the line containing "Form_BeforeUpdate" is highlighted in the Current
event.

Any ideas? I am totally stumped - this is way out of my comfort zone!

Thanks!
 
The Form_BeforeUpdate procedure has an integer parameter associated with it:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Even though you're not going to make use of that parameter, you still need
to include it in the call to the sub:

Private Sub Form_Current()
Dim intUnnecessary As Integer

If Not Me.NewRecord Then
Form_BeforeUpdate intUnnecessary
End If

End Sub

or

Private Sub Form_Current()
Dim intUnnecessary As Integer

If Not Me.NewRecord Then
Call Form_BeforeUpdate(intUnnecessary)
End If

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
That helped... now, the only problem is:

When I open the form, only the first line updates..... for the form to
autopopulate everything over 95%, you have to click on each line! (I put the
code in Before Update, BTW).

What can I do now??

THANKS!!
 
You didn't tell us you had a continuous form. You will need to use a query
as the underlying record source. In that query, add a column like:

Reason_Code: IIf([Percentage_Complete] > 0.95, "CNF", "")
 
Forgive me for being such a pain, but I don't understand what I am supposed
to do...

The current record source for this form is a table. The table gets its data
from an append query. I am not sure what to modify in order to get it to
auto-fill. Should I create a query based on the table, or what?

I know this probably sounds really clunky to you, but I am pretty new to
forms and still trying to feel my way around....

Thanks for the help!

Arvin Meyer said:
You didn't tell us you had a continuous form. You will need to use a query
as the underlying record source. In that query, add a column like:

Reason_Code: IIf([Percentage_Complete] > 0.95, "CNF", "")
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


GoBrowns! said:
That helped... now, the only problem is:

When I open the form, only the first line updates..... for the form to
autopopulate everything over 95%, you have to click on each line! (I put
the
code in Before Update, BTW).

What can I do now??

THANKS!!
Click to expand...
Click to expand...
 
Yes, you will need to build a query based on the table, and use the query as
the recordsource for your form. Add the following column to your query:

Reason_Code: IIf([Percentage_Complete] > 0.95, "CNF", "")

which might then look something like this in SQL view:
Select *, IIf([Percentage_Complete] > 0.95, "CNF", "") A Reason_Code
From MyTable;
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


GoBrowns! said:
Forgive me for being such a pain, but I don't understand what I am
supposed
to do...

The current record source for this form is a table. The table gets its
data
from an append query. I am not sure what to modify in order to get it to
auto-fill. Should I create a query based on the table, or what?

I know this probably sounds really clunky to you, but I am pretty new to
forms and still trying to feel my way around....

Thanks for the help!

Arvin Meyer said:
You didn't tell us you had a continuous form. You will need to use a
query
as the underlying record source. In that query, add a column like:

Reason_Code: IIf([Percentage_Complete] > 0.95, "CNF", "")
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


GoBrowns! said:
That helped... now, the only problem is:

When I open the form, only the first line updates..... for the form to
autopopulate everything over 95%, you have to click on each line! (I
put
the
code in Before Update, BTW).

What can I do now??

THANKS!!

:

The Form_BeforeUpdate procedure has an integer parameter associated
with
it:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Even though you're not going to make use of that parameter, you still
need
to include it in the call to the sub:

Private Sub Form_Current()
Dim intUnnecessary As Integer

If Not Me.NewRecord Then
Form_BeforeUpdate intUnnecessary
End If

End Sub

or

Private Sub Form_Current()
Dim intUnnecessary As Integer

If Not Me.NewRecord Then
Call Form_BeforeUpdate(intUnnecessary)
End If

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That did not work either.

When I put both pieces of code in, I get the error:

"Compile error: Argument not optional"

and the line containing "Form_BeforeUpdate" is highlighted in the
Current
event.

Any ideas? I am totally stumped - this is way out of my comfort
zone!

Thanks!

:

I'd use the BeforeUpdate evernt of the form and the Current event
of
the
form. In the Current event, you also need to check if it's a New
Record,
and
then just call the other event. Like:

Private Sub Form_Current()

If Not Me.NewRecord Then
Form_BeforeUpdate
End If

End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I am still having trouble. I tried putting the following in both
the
Before
Update and On Open events... thinking that I needed these fields
to
already
be filled in when the user opens the form....

Private Sub Form_Open(Cancel As Integer)
If Len(Me.Reason_Code & vbNullString) = 0 Then
If Me.Percentage_Complete > 0.95 Then
Me.Reason_Code = "CNF"
End If
End If
End Sub

I get an error that I cannot assign a value to this object. Can
you
please
help?

The second part of the code you gave me.... I am assuming that is
to
fill
in
my "Remaining" field and not related to allowing me to
auto-populate
a
reason
code.

Thanks so much for all your help!!!!!!!!!!

:

message
Not even sure if this is possible, but here goes.

I have a form that allows users to input a "reason code" for
each
line
of
data. Is there a way to:

Have the form automatically code items that have a percentage
delivery
higher than 95%?

Have the form automatically code items that are blank in the
field
"remaining" with the value that appeared in the "required"
field?

Please help - if it is indeed possible!

Let me know if any other information is needed.

In the form's BeforeUpdate event use something like (untested):

Private Sub Form_BeforeUpdate (Cancel As Integer)
If Len(Me.txtReasonCode & vbNullString) = 0 Then
If Me.txtPercentageDelivery > than .95 Then
Me.txtReasonCode = "Whatever"
End If
End If

If Len(Me.txtRemaining & vbNullString) = 0 Then
Me.txtRemaining = Me.txtRequired
End If
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Click to expand...
Click to expand...
Click to expand...
 
Back
Top