Filling a new record with values from a previous record

  • Thread starter Thread starter Linda
  • Start date Start date
L

Linda

I have the following code in the oncurrent event of my
form. I'm using Access 97.
What I want to do is that whenever my users enter a new
record that the values previously entered in the fields
hotel and arrivaldate ger carried over when creating a
new record. Strangely enough my code does not seem to
work.

Does anyone see what I'm doing wrong here?

Dim rs As Recordset
Set rs = Me.RecordsetClone


If rs.EOF Or Not Me.NewRecord Then
'don't do anything if there's no records or it is a
new record

Else
With rs
.MoveNext
Me![txtHotel] = .Fields("Hotel")
Me![txtArrivalDate] = .Fields("ArrivalDate")
End With
End If
 
Linda,

I tested your code and it does work (with the addition of a DAO refernce
and the use of a DAO recordset, didn't work for me otherwise), but:
Your MoveNext method actually selects the first record in the recordset, not
the last one on your form, and it brings through the field values from that
one. Also, if you do not close the recordset (not shown in your code
snippet), it advances one record each time you add a new one rather than
always picking up the first one. Is this the problem you've been having?
Generally, it's kind of tricky to get to the correct record that way. Since
it's only two controls you're trying to copy through, I would suggest a
different approach: use two global variables, say, vHotel and vArrDate, and
run a piece of code like:

If Me.NewRecord Then
Me![txtHotel] = vHotel
Me![txtArrivalDate] = vArrDate
End If
vHotel = Me![txtHotel]
vArrDate = Me![txtArrivalDate]

in the form's On Current event. What it does is it stores the current
record's values in the two global variables every time, and if it's a new
record it pulls in the ones stored from the previous one.
You should also use the Before Update event of each of the two controls to
store its new value to the corresponding global variable every time it's
changed, so if you go to a new record and change those, the new ones will be
carried along.

HTH,
Nikos
 
Nikos,

This works fine for my text boxes but it does not seem
to work for my combo-boxes, I have no reason why?

Thanks.
-----Original Message-----
Linda,

I tested your code and it does work (with the addition of a DAO refernce
and the use of a DAO recordset, didn't work for me otherwise), but:
Your MoveNext method actually selects the first record in the recordset, not
the last one on your form, and it brings through the field values from that
one. Also, if you do not close the recordset (not shown in your code
snippet), it advances one record each time you add a new one rather than
always picking up the first one. Is this the problem you've been having?
Generally, it's kind of tricky to get to the correct record that way. Since
it's only two controls you're trying to copy through, I would suggest a
different approach: use two global variables, say, vHotel and vArrDate, and
run a piece of code like:

If Me.NewRecord Then
Me![txtHotel] = vHotel
Me![txtArrivalDate] = vArrDate
End If
vHotel = Me![txtHotel]
vArrDate = Me![txtArrivalDate]

in the form's On Current event. What it does is it stores the current
record's values in the two global variables every time, and if it's a new
record it pulls in the ones stored from the previous one.
You should also use the Before Update event of each of the two controls to
store its new value to the corresponding global variable every time it's
changed, so if you go to a new record and change those, the new ones will be
carried along.

HTH,
Nikos




I have the following code in the oncurrent event of my
form. I'm using Access 97.
What I want to do is that whenever my users enter a new
record that the values previously entered in the fields
hotel and arrivaldate ger carried over when creating a
new record. Strangely enough my code does not seem to
work.

Does anyone see what I'm doing wrong here?

Dim rs As Recordset
Set rs = Me.RecordsetClone


If rs.EOF Or Not Me.NewRecord Then
'don't do anything if there's no records or it is a
new record

Else
With rs
.MoveNext
Me![txtHotel] = .Fields("Hotel")
Me![txtArrivalDate] = .Fields ("ArrivalDate")
End With
End If
Click to expand...


.
Click to expand...
 
Linda,

Just guessing here, but it might well be that the value you are trying to
set the combo to is what you see rather than what it should! Let me explain
with an example: suppose you have a table with states (called tblStates),
like:
StateID State
CA California
FL Florida
IL Illinois
NJ New Jersey
.... ...

and a combo box on it, with properties set to:
RowSource: SELECT [StateID], [State] FROM tblStates
Column count 2
Bound Column 1
Column Widths 0;2;

These settings result in your combo box showing the state name, e.g.
Florida, while the value it returns is that in the first column, i.e. FL,
which you don't see in it on screen.Likewise, in order to set its value to
something, that something should be the value in the first, invisible
column, e.g. FL; if you try to set it to Florida, which is what you see, it
fails because this value is not in the list of values of the bound column 1.

Could this be your problem?

HTH,
Nikos

Linda said:
Nikos,

This works fine for my text boxes but it does not seem
to work for my combo-boxes, I have no reason why?

Thanks.
-----Original Message-----
Linda,

I tested your code and it does work (with the addition of a DAO refernce
and the use of a DAO recordset, didn't work for me otherwise), but:
Your MoveNext method actually selects the first record in the recordset, not
the last one on your form, and it brings through the field values from that
one. Also, if you do not close the recordset (not shown in your code
snippet), it advances one record each time you add a new one rather than
always picking up the first one. Is this the problem you've been having?
Generally, it's kind of tricky to get to the correct record that way. Since
it's only two controls you're trying to copy through, I would suggest a
different approach: use two global variables, say, vHotel and vArrDate, and
run a piece of code like:

If Me.NewRecord Then
Me![txtHotel] = vHotel
Me![txtArrivalDate] = vArrDate
End If
vHotel = Me![txtHotel]
vArrDate = Me![txtArrivalDate]

in the form's On Current event. What it does is it stores the current
record's values in the two global variables every time, and if it's a new
record it pulls in the ones stored from the previous one.
You should also use the Before Update event of each of the two controls to
store its new value to the corresponding global variable every time it's
changed, so if you go to a new record and change those, the new ones will be
carried along.

HTH,
Nikos




I have the following code in the oncurrent event of my
form. I'm using Access 97.
What I want to do is that whenever my users enter a new
record that the values previously entered in the fields
hotel and arrivaldate ger carried over when creating a
new record. Strangely enough my code does not seem to
work.

Does anyone see what I'm doing wrong here?

Dim rs As Recordset
Set rs = Me.RecordsetClone


If rs.EOF Or Not Me.NewRecord Then
'don't do anything if there's no records or it is a
new record

Else
With rs
.MoveNext
Me![txtHotel] = .Fields("Hotel")
Me![txtArrivalDate] = .Fields ("ArrivalDate")
End With
End If
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top