Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatic data entry (2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatic data entry (2000)

    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.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic data entry (2000)

    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.

  4. #4
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic data entry (2000)

    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

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Automatic data entry (2000)

    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>

  6. #6
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic data entry (2000)

    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.

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic data entry (2000)

    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
    Attached Files Attached Files

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Automatic data entry (2000)

    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
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatic data entry (2000)

    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?
    Charlotte

  10. #10
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic data entry (2000)

    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

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatic data entry (2000)

    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.
    Charlotte

  12. #12
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic data entry (2000)

    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

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatic data entry (2000)

    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
    Charlotte

  14. #14
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic data entry (2000)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •