Automatic data entry (2000)

[Egg 'n' Bacon]

I'm trying to get a data entry form to automatically populate a field (or possibly 2 fields), based on the value placed in another.
Example;
field1=date; field2=value(a,b,orc); field3(the one I need help with)=if [value]="a",[date]+1 month, or if [value]="b",[date]+3 months, or if [value]="c",[date]+6 months

Once I've got this, I hope to be able to do any other similar ones. But first I hope this isn't too heavy on VBA, as I've had almost zero use with it.

TIA
EnB

[charlotte]

The question is whether you need those other fields to be editable. If so, then you have to use code. If not, you can use some built-in functions. For instance, you could put the following in the controlsource of field3:

<pre>=iif([field2]="a", dateadd("m",1,[field1]), iif([field2]="b", dateadd("m",3,[field1]), dateadd("m",6,[field1])))</pre>


This assumes that [value] in your original code was whatever you put in field2 and [date] was whatever you put in field1.

[Egg &#39;n&#39; Bacon]

Thank you Charlotte. I thought it was something on those lines, but I just didn't seem to get anywhere. Now I can. Thanks again.

[Egg &#39;n&#39; Bacon]

Sorry to exhume this old topic, but things have changed & now the built in functions are no longer usable. Can anyone give me some pointers on what code would be appropriate for this. I guess it'll be under "After Update", with not dissimilar to that Charlotte posted.

TIA
Paul

[patt]

Hi Paul, long time no hear.

What do you really want?
Why doesn't Charlotte's code work anymore?

Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

[Egg &#39;n&#39; Bacon]

Just like Charlotte said,
"The question is whether you need those other fields to be editable. If so, then you have to use code"
& now users require those other fields to be editable. The code put straight into the control source of the text box does still work, in as far as it generates the relevant new date. But what it doesn't do is record that result in the record [img]/forums/images/smilies/sad.gif[/img] I did try making the box non-visible & out of tab order & those sort of things, but I'm now thinking it's down to putting some code into the VBA editor.

[Egg &#39;n&#39; Bacon]

I've extracted a sample of what I'm trying to do here. The issue is with Sfrm-AddPoints; trying to get the non-visible field to enter the result into the corresponding field in the Add Points table.

TIA
Paul

[MarkD]

I took look at sample db & made some changes; modified db is attached. Made change to main form (synchronized combo with current record & modified After Update event to specify object type for recordset object). Modified tables to turn off Subdatasheet property (it can slow things down significantly) and changed name of "Date" field in AddPoints table to "StartDate" (naming field "Date" is not good idea, especially if you are going to refer to field in date related functions). Modified subform so that EndDate textbox is bound to EndDate field, textbox is locked so cannot be updated directly, ditched the IIf expression (a control using an expression as its ControlSource cannot be bound to field in underlying table). EndDate is updated by the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me.Points) And Not IsNull(Me.StartDate) Then
UpdateEndDate
End If
End Sub

Private Sub Points_AfterUpdate()
If Not IsNull(Me.Points) And Not IsNull(Me.StartDate) Then
UpdateEndDate
End If
End Sub

Private Sub UpdateEndDate()
With Me
Select Case .Points
Case "G"
.EndDate = DateAdd("m", 6, .StartDate)
Case "O"
.EndDate = DateAdd("m", 9, .StartDate)
Case "R"
.EndDate = DateAdd("m", 12, .StartDate)
End Select
End With
End Sub

I'm not sure if these are right intervals, if not modify DateAdd function to reflect correct monthly interval. You'd probably need to add error handling or other validation, etc, this is just simple example of how to handle updating the EndDate field. See attached sample db to see how this works.

HTH

[charlotte]

Actually, my remark has nothing to do with your problem. The value isn't getting added to the table because you haven't created any mechanism to write it to the table. You don't even have any bound field on the form that can reference it to capture the value.

You don't always want there to be an EndDate do you? So how are you planning to tell the system *when* to insert the EndDate? If selecting a person from the dropdown is all the trigger you want to insert an EndDate, then just change the name of your hidden EndDate control to txtEndDate and add a visible control in the same location bound to the EndDate field. When you select a name (or do whatever else it is you want to do to trigger the end date), add a line of code to the parent form routine like this:

<pre>If IsNull(Me![Sfrm-AddPoints]!EndDate) Then
Me![Sfrm-AddPoints]!EndDate = Me![Sfrm-AddPoints]!txtEndDate
End If</pre>


That will put the calculated EndDate in but the control will be editable, so the user can change that date.

Does that do what you want?

[Egg &#39;n&#39; Bacon]

Thanks for that Mark it looks rather like what I need, but for some reason, I got a compile error;
"User defined type not defined" & the editor opened.

So I gave Charlottes' approach, which maybe a little more elegant. But that seemed a little beyond me as well! I tried entering the code and that came to nought. I was beginning to think dark thought then, so I just pasted it in instead (I put it in the 'on change' for the name combo) & that came close. A little change (dropping out the If; the end date shouldn't be left empty really) & it works!

Thank-you all very much

NB Charlotte, I guess I misread your earlier instructions & I am a struggling amateur, please be gentle with me, my ego couldn't take it [img]/forums/images/smilies/smile.gif[/img]

Thanks again

Paul

[charlotte]

Paste the code into the AfterUpdate event of the combobox instead of the Change event. Then it will only execute when the user has made a selection, not every time they type in a letter. The AfterUpdate event happens only once for the control, but the Change event can happen over and over and it will affect whatever the current record may be, even though that isn't the record you want to set the end date for.

[Egg &#39;n&#39; Bacon]

OK, I was hoping the users would use the mouse for the combo, but on reflection, you're dead right; never trust them to do what you expect!
I'm not sure where to place it within the default combo code (why I put it in the 'cange event <img src=/S/smile.gif border=0 alt=smile width=15 height=15> ). This is what's in place now;

Private Sub Combo4_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[DRIVER_ID] = '" & Me![Combo4] & "'"
Me.Bookmark = rs.Bookmark
End Sub

& here's the single line that does the job;

Me![Sfrm-AddPoints]!EndDate = Me![Sfrm-AddPoints]!TxtEndDate

[charlotte]

Try this:

Private Sub Combo4_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[DRIVER_ID] = '" & Me![Combo4] & "'"
<font color=448800>' using NoMatch keeps you from changing the wrong record
' in case the value doesn't find a match</font color=448800>
If Not rs.NoMatch then
Me.Bookmark = rs.Bookmark

if IsNull(Me![Sfrm-AddPoints]!EndDate ) Then
Me![Sfrm-AddPoints]!EndDate = Me![Sfrm-AddPoints]!TxtEndDate
End If
End If
End Sub

[Egg &#39;n&#39; Bacon]

Thank you again Charlotte, I'm up & running now.
Cheers