Adding an extra record base on value selected on current recor

MS Office Access


  • 1. Disable editing combo box
    Hi all, How do I disable the the combo box from editing. The combo box containg value that I don't users to edit or type anything in the combo box. I just want the user to click on it. Thanks for the help... mike
  • 2. How do I specify the number of continuous forms to show?
    I have a form that is set as follows: DefaultView = Continuous Forms BorderStyle = Dialog (I don't want user to resize it) AutoResize = True AutoCenter = True All the control box and those types of buttons are off PopUp = True For some reason it always opens with only one record showing. There are many, and I want the height to be such that 5 or 6 of the records (continuous forms) show in the detail section. When I set it to Sizeable, I resize it and click save. Then when I change it back to Dialog and run it it's back to 1 record. I have a similar form in the program I'm working on and it works as I want it to. I can't seem to see any difference between the 2. Is there a way to tell access how many continuous forms to show? Any advice would be appreciated. Thanks, Keith
  • 3. Fieldname with space and OnClick procedure
    I get an error using my field "Print Date" for a str...this is what I have: Dim strField As String 'Name of date field. Dim strWhere As String 'Where condition strField = "Print Date" strWhere = strField & " < " & Format(Me.txtEndDate) The statement ends up like: "Print Date < 11/01/03" and gives me a syntax error. I tried Print_Date and it doesn't see the field. How do I code a fieldname with a space? Thanks in advance!

Re: Adding an extra record base on value selected on current recor

Postby U3RldmUgU2FuZm9yZA » Thu, 15 May 2008 14:13:00 GMT

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

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?

Steve S
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Re: Adding an extra record base on value selected on current recor

Postby Jan T. » Sat, 24 May 2008 04:58:44 GMT

ell 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/" &
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("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
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

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

Jan T.

"Steve Sanford" <limbim53 at yahoo dot com> skrev i melding
news: XXXX@XXXXX.COM ...

Re: Adding an extra record base on value selected on current recor

Postby U3RldmUgU2FuZm9yZA » Sat, 24 May 2008 12:53:00 GMT


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

Steve S
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

"Jan T." wrote:

Re: Adding an extra record base on value selected on current recor

Postby Jan T. » Mon, 26 May 2008 14:55:34 GMT

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


"Steve Sanford" <limbim53 at yahoo dot com> skrev i melding
news: XXXX@XXXXX.COM ...

Similar Threads:

1.Adding an extra record base on value selected on current record

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

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.


2.Default value of field in current record to be value of last recor

I am new to VBA and am learning.  But I need to get beyond this problem to 
get up and running.

I want to be able to allow the user to enter a date in a field on a form 
(call it a posting date) in the first record of the data-entry-session (not 
necessarily the first record in the underlying table).  Once that is done, I 
want the subsequent new record to be able to use the date from the user's 
first record as the default value for the current (new) record.

If the user should change the date after, say the third or fourth record, 
the program should remember the new date and make it the new default value 
for the user's next (new) and subsequent records until it is changed again by 
the user.

I don't seem to be able to find any literature on this 'ditto' dillema!
I would appreciate any input on this problem.  Thanks in advance.

Frank M
Hilton Head Island, SC

3.Adding new records based on current record

I am trying to add three new records to a table, based on the record that a 
user has just created using a form.  When the new record is created, I want 
to add three more records with different record types and clear some amount 
and date fields so the records are fresh for later calculations.  I have 
written a macro that accomplishes adding the records using a series of 
queries, but the fields are not in the correct order when I do the append.  
This seems to be a crude way to accomplish this task.  Is there a better way? 
 I have limited Visual Basic skills.

Karen Miller
Kansas City, MO

4.Select Q based on another Select Q (Add record problems)


I have a Q (B) which is based on another Q (A) and a table (tblC). I have
created a relationship between QB and table tblC (autonumber field).

It seams that it not possible to add new records into any of the tables
involved. And to be able to add new records I need to create a completly new
query using all tables and criteras as I already have in QA.

Is this the way it sopose to work or am I doing something wrong here? Is
there a way around this?

It would be so nice to just have ONE QA and then base several other Q on
this to be used as recordsources for form. Which is the case with QB (I DO
have other names on my queries but to simplifye this msg I renamed them to
QA and QB).

// Niklas

5.How do I select a record and if no record exists go to a new recor

(Newbie request)

I have defined a table where IDNumber is Indexed (Dups OK) and ReportDate is 
not indexed, but I want to create a query that returns the other data based 
on these fields and updates the form.  If there is no record for that 
IDNumber and ReportDate, I want it to select a new record to update.

Thanks in advance.

6. add new record based on selected record

7. Subtract a field from a previous record from the current recor

8. Selecting Records base on current month

Return to MS Office Access


Who is online

Users browsing this forum: No registered users and 99 guest