Please Formula/Text/form Guru needed

[Guest]

Hello all,

I have one that is so simple that I need to trouble the masters with this.

I have a form, that when a dispatcher enters a stop number it needs to
automatically fill in the projected cost.
a Stop 1 is Automatically 380.00 US and every other stop is 50.00 US.

The field needs to be editable so if there is a Cost change or even a
sequence change (late add on orders or routes deleted) I need the cost to
update with this change.

I have tried before/after/current , but still am not sure if I am doing this
right. any help is greatly appreciated.

 

[Allen Browne]

Use the BeforeInsert event of the form.
DLookup() the table to see if any records already exist for this entry.
If the result IsNull(), assign $380, otherwise $50.

For help on DLookup(), see:
http://allenbrowne.com/casu-07.html

 

[Guest]

I like the idea but I am a little confused as to how to make it work.

I undestand DLookup, i believe you helped me out a while back, but if I
place it on the Form how will it update the field? is there somethings I
should change in the Text Field Events?

Thanks in advance for your patients, but this is nothing like excel.

Charlie
--
-The Novice
Learn Today, Teach Tomorrow

Great Success is ones ability to ask for Help.

Use the BeforeInsert event of the form.
DLookup() the table to see if any records already exist for this entry.
If the result IsNull(), assign $380, otherwise $50.

For help on DLookup(), see:
http://allenbrowne.com/casu-07.html

 

[Allen Browne]

The specifics will depend on how you determine whether there is already a
record for "this" entry of not, i.e. how you determine whether the charge is
$380 or $50.

The example below assumes this is a subform, linked to the main form on
field MainID (in the main form) and SubID (in the subform.) The first row in
the subform is charged as $380, and subsequent rows at $50.

1. Open the subform in design view.

2. Set the Before Insert property of the form (i.e. the subform) to:
[Event Procedure]

3. Click the Build button (...) beside this.
Access opens the code window.

4. Enter the code so it looks like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

If IsNull(Me.Parent!MainID) Then
Cancel = True
MsgBox "Enter a record in the main form first."
Else
strWhere = "SubID = " & Me.Parent!MainID
varResult = DLookup("SubID", "Table2", strWhere)
If IsNull(varResult) Then
Me.Charge = 380
Else
Me.Charge = 50
End If
End If
End Sub

 

[Guest]

you are the man!
--
-The Novice
Learn Today, Teach Tomorrow

Great Success is ones ability to ask for Help.

The specifics will depend on how you determine whether there is already a
record for "this" entry of not, i.e. how you determine whether the charge is
$380 or $50.

The example below assumes this is a subform, linked to the main form on
field MainID (in the main form) and SubID (in the subform.) The first row in
the subform is charged as $380, and subsequent rows at $50.

1. Open the subform in design view.

2. Set the Before Insert property of the form (i.e. the subform) to:
[Event Procedure]

3. Click the Build button (...) beside this.
Access opens the code window.

4. Enter the code so it looks like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

If IsNull(Me.Parent!MainID) Then
Cancel = True
MsgBox "Enter a record in the main form first."
Else
strWhere = "SubID = " & Me.Parent!MainID
varResult = DLookup("SubID", "Table2", strWhere)
If IsNull(varResult) Then
Me.Charge = 380
Else
Me.Charge = 50
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I like the idea but I am a little confused as to how to make it work.

I undestand DLookup, i believe you helped me out a while back, but if I
place it on the Form how will it update the field? is there somethings I
should change in the Text Field Events?

Thanks in advance for your patients, but this is nothing like excel.

Charlie
--
-The Novice
Learn Today, Teach Tomorrow

Great Success is ones ability to ask for Help.