Adding an extra record base on value selected on current record

  • Thread starter Thread starter Jan T.
  • Start date Start date
J

Jan T.

Hi. Working on a split db 2000 and have a form with a subform where
I want some special action to take place. (frontend on local machine -
and backend on shared folder).

On my main form I also have a sub form. Applying a new record in
the sub form I want something to happen if the user selects a spesific
choise from a combo box.

This is what I want to do: First change the values in some of the fields
in the currentrecord. Then I want to have this record saved and then
I want automatically add an new record with custom calculated values
in it.

The reason I want it automatically inserted, is of the complicated
calculations.

In code behind the form, I can change some og the current fields
with code like:

me.SeconField = 3.5
me.ThirdField = 2
me.FourthField = 6 and so forth...

However, I cannot get this record saved.
Next I cannot automatically add a new record and then save that record.

Any help with this is much appreciated.

Regards
Jan
 
However, I cannot get this record saved.

To save the current record:

If Me.Dirty Then
Me.Dirty = False
End If
Next I cannot automatically add a new record and then save that record.
Click to expand...

This depends on the recordsource; is it a table, or a query with more that
one table?

--You could use an Append query.
--You could open a recordset and add the new record.
--You could save the data to variables, then in the form, goto a new record,
add the data (from the memory variables) and save the new record.

When/where are the custom calculated values calculated?

HTH
 
Hei, and thank you for your answer! Sorry I could not come back to you
before.

I have based the subForm on a query with only one table where the records
are filtered based on a criteria. That is why I use a query in stead of the
table.
I don't want the subform to be filled with lots of records that is not of
any
interest. Does that make a difference?

Jan
 
Sorry for my late reply.
However, to save the current record worked fine.
Then you ask if the recordsource is a table or a query?
It is a query where some records is filtered out. Does that affect
the solution?

Jan
 
No, I rarely use a table as the recordsource of a form (or subforms, combo
boxes,...).

So what is the table name and field names? Where does the data to put in
the fields come from? The results of the calculations?

Do you know how to do this using a recordset or SQL?

HTH
 
Well after some difficulties I ended up with this code (tried to translate
it to english):

Private Sub RealizeTo_AfterUpdate()
Dim EmpId As Long
Dim Reason As Long
Dim strDep As String
Dim WrkDate As Date
Dim myDate As Date
Dim dtmFrom As Date
Dim dtmUntil As Date
Dim W0Prs As Double
Dim W50Prs As Double
Dim W100Prs As Double
Dim W133Prs As Double
Dim Realz2 As Integer
Dim TmeStp As Date
Dim rs As New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection
myDate = DateSerial(Year(Date), 12, 31)
' Dato settes automatisk etter valgt alternativ. Blankt hvis Avspasering.
With Me ' 'myDate ' DateSerial(Year(Date), Month(Date) + 1, 15)
If .RealizeTo.Value = 2 Or .RealizeTo.Value = 4 Or .RealizeTo.Value
= 5 _
Or .RealizeTo.Value = 6 Then
.DateToRealize = DateSerial(Year(Date), Month(Date) + 1, 15)
ElseIf .RealizeTo.Value = 1 Or .RealizeTo.Value = 3 Then
If .RealizeTo.Value = 3 Then .DateToRealize =
DateSerial(Year(Date), 12, 31)
If .RealizeTo.Value = 1 Then _
.DateToRealize = InputBox("What date do you want to take
time off: mm\dd\yyyy", _
"Date to take time off; mm\dd\yyyy", "31/12/" &
Year(Date))
EmpId = .Emplid
strDep = .Department
Reason = .ReasonForOvertime
WrkDate = .WorkDate
W0Prs = .Wrk0Prosent
W50Prs = .Wrk50Prosent
W100Prs = .Wrk100Prosent
W133Prs = .Wrk133Prosent
'Realz2 = .RealizeTo
TmeStp = .Registered
.Wrk50Prosent = 0
.Wrk100Prosent = 0
.Wrk133Prosent = 0
.Wrk0Prosent = (.Until - .From) * 24
If Me.Dirty = True Then Me.Dirty = False
If (MsgBox("You chose " & .RealizeTo.Text & "." & vbCrLf & _
"Bla, bla --some text bla....." & vbCrLf & vbCrLf & _
"Bla, bla --some text bla....." & vbCrLf & _
"Bla, bla --some text bla......", vbCritical + vbYesNo, _
"Overtime hours")) = vbYes Then
rs.Open "myTableName", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
rs.AddNew
rs("EmployeeId") = EmpId
rs("WorkDate") = WrkDate
rs("From") = "00:00" 'dtmFrom
rs("Until") = "00:00:00" 'dtmUntil
rs("Wrk0Prosent") = W0Prs
rs("Wrk50Prosent") = W50Prs
rs("Wrk100Prosent") = W100Prs
rs("Wrk133Prosent") = W133Prs
rs("RealizeTo") = 5 'Realz2
rs("TimeStamp") = TmeStp
rs("ReasonForOvertime") = Reason
rs("DateToRealize") = DateSerial(Year(Date), Month(Date) +
1, 15)
rs("User") = fGetUserName
rs("Department") = strDep
rs.Update
.Requery
End If
End If
End With
End Sub

.... and this works fine for my purpose. (The calculations are very complex
and resides in
an other function and event. I won't include them here).

Well, hope somebody can use this code snippet if they turn into same
problem.

Anyway, thank you for your helping, Steve S. I was stuck for a while.. :)

Jan T.
 
Jan,

I can't tell if you have the code working or not.... I hope it is.

It looks to me like the only time a new record will be created is when
.RealizeTo = 1 or .RealizeTo = 3


HTH
 
You are quit right. The new record is just added if .RealizeTo = 1 or
..RealizeTo = 3.
Otherwise, there is no need for a new record.

Jan
 
Back
Top