Getting data from another table

  • Thread starter Thread starter John Doe
  • Start date Start date
J

John Doe

I have a date field in a form that I would like to automatically fill with a
date. The date should be the date from a field on the form (IncidentDate)
increased by a certain number of years. The number of years to increase it
will depend upon two other fields in the table (StateID and IncidentTypeID -
both of which are lookups from their respective tblStates and
tblIncidentTypes). The number of years to increase the IncidentDate is
stored in another table (tblSOL) and varies with the StateID and the
IncidentTypeID. The number is stored in a field called NumberOfYears.

I've tried a lot of things including queries, functions, SQL statements,
etc. and nothing is working.

Any ideas greatly appreciated.

Thank you,

Penn
 
I have a date field in a form that I would like to automatically fill with a
date. The date should be the date from a field on the form (IncidentDate)
increased by a certain number of years. The number of years to increase it
will depend upon two other fields in the table (StateID and IncidentTypeID -
both of which are lookups from their respective tblStates and
tblIncidentTypes). The number of years to increase the IncidentDate is
stored in another table (tblSOL) and varies with the StateID and the
IncidentTypeID. The number is stored in a field called NumberOfYears.

I've tried a lot of things including queries, functions, SQL statements,
etc. and nothing is working.
Click to expand...

I'd suggest using the Form's BeforeUpdate event - or, not quite as
simply, the AfterUpdate events of both StateID and IncidentTypeID. The
expression might be something like

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any validation code goes here>
Me!fieldname = DateAdd("yyyy", _
DLookUp("[NumberOfYears]", _
"[tblSOL]", _
"[StateID] = " & Me.StateID & _
" AND IncidentTypeID = " & Me.IncidentTypeID),
[IncidentDate])
End Sub

If you do it in the AfterUpdate events of StateID or IncidentTypeID,
you'll need to allow for the possibility that one or the other is
null.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thank you very much, John. I'll give it a try.

Penn

John Vinson said:
I have a date field in a form that I would like to automatically fill with
a
date. The date should be the date from a field on the form (IncidentDate)
increased by a certain number of years. The number of years to increase
it
will depend upon two other fields in the table (StateID and
IncidentTypeID -
both of which are lookups from their respective tblStates and
tblIncidentTypes). The number of years to increase the IncidentDate is
stored in another table (tblSOL) and varies with the StateID and the
IncidentTypeID. The number is stored in a field called NumberOfYears.

I've tried a lot of things including queries, functions, SQL statements,
etc. and nothing is working.
Click to expand...

I'd suggest using the Form's BeforeUpdate event - or, not quite as
simply, the AfterUpdate events of both StateID and IncidentTypeID. The
expression might be something like

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any validation code goes here>
Me!fieldname = DateAdd("yyyy", _
DLookUp("[NumberOfYears]", _
"[tblSOL]", _
"[StateID] = " & Me.StateID & _
" AND IncidentTypeID = " & Me.IncidentTypeID),
[IncidentDate])
End Sub

If you do it in the AfterUpdate events of StateID or IncidentTypeID,
you'll need to allow for the possibility that one or the other is
null.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Thanks again, John.

Here's the code I ended up with and it works beautifully. Still need to set
it to the two textboxes.

Dim strDLookUp As String
strDLookUp = DLookup("[NumberOfYears]", "[tblSOL]", "[StateID] = " &
Me.StateID & " and SOLIdentID = " & Me.cboSOLIdentID)
If (Not IsNull(strDLookUp)) Then
MsgBox "Number of Years to add = " & strDLookUp
Me.txtSOLDate = DateSerial(Year([IDate]) + Val(strDLookUp),
Month([IDate]), Day([IDate]))
 
Back
Top