Repeating selected fields into the next record

[CCT]

I import data from excel into access then via a query into an existing table.
Each record has a hundred fields or so and typically a thousand records per
import. I need to fill out the fields not filled by the excel worksheet
import. Many of the empty fields will have repeating data though a varying
number of records. (please no lectures about relational databases - I
understand them well and that is not the problem here) I want to place a
command button on the form so that when pressed all of the fields in the
current record with an entry in the Tag property will grab the data from the
previous record. I have used the following code and variations without
success.

Dim ctl As Control

For Each ctl In Me.Controls
Me.ActiveControl.SetFocus
If ctl.Tag = "RepeatPrevious" Then
SendKeys "^(')"

End If
Next

I know the problem is with the Sendkeys. While it works on a single field as
in:

Private Sub Command98_Click()

Me![*Auction Title*].SetFocus
SendKeys "^(')"

End Sub

It does not work when I use it in the first example with the tag property. I
searched extensively for a work around with out success. Any have any ideas
on how I might this work? I really don't want the input operator to press a
command button or do a Ctrl+' to fill in the data. Thanks in advance

CCT

 

[Graham Mandeno]

Hi CCT

My advice is NEVER to use SendKeys.

Use the form's RecordsetClone to navigate to the previous record, and then
copy the value from each field to the current record. Something like this
ought to do the trick:

With Me.RecordsetClone
.Bookmark = Me.Bookmark
.MovePrevious
For Each ctl In Me.Controls
If ctl.Tag = "RepeatPrevious" Then
ctl.Value = .Fields(ctl.ControlSource)
End If
Next
End With

You might like to check that ctl.Value is Null, so that you don't
inadvertently overwrite values that are already entered - or perhaps that is
not a concern.

 

[CCT]

That is exactly what I was looking for. While many said to never use Sendkeys
I was unable to find a viable work around. Your method worked exactly as I
had hoped . Many thanks.

Hi CCT

My advice is NEVER to use SendKeys.

Use the form's RecordsetClone to navigate to the previous record, and then
copy the value from each field to the current record. Something like this
ought to do the trick:

With Me.RecordsetClone
.Bookmark = Me.Bookmark
.MovePrevious
For Each ctl In Me.Controls
If ctl.Tag = "RepeatPrevious" Then
ctl.Value = .Fields(ctl.ControlSource)
End If
Next
End With

You might like to check that ctl.Value is Null, so that you don't
inadvertently overwrite values that are already entered - or perhaps that is
not a concern.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I import data from excel into access then via a query into an existing
table.
Each record has a hundred fields or so and typically a thousand records
per
import. I need to fill out the fields not filled by the excel worksheet
import. Many of the empty fields will have repeating data though a varying
number of records. (please no lectures about relational databases - I
understand them well and that is not the problem here) I want to place a
command button on the form so that when pressed all of the fields in the
current record with an entry in the Tag property will grab the data from
the
previous record. I have used the following code and variations without
success.

Dim ctl As Control

For Each ctl In Me.Controls
Me.ActiveControl.SetFocus
If ctl.Tag = "RepeatPrevious" Then
SendKeys "^(')"

End If
Next

I know the problem is with the Sendkeys. While it works on a single field
as
in:

Private Sub Command98_Click()

Me![*Auction Title*].SetFocus
SendKeys "^(')"

End Sub

It does not work when I use it in the first example with the tag property.
I
searched extensively for a work around with out success. Any have any
ideas
on how I might this work? I really don't want the input operator to press
a
command button or do a Ctrl+' to fill in the data. Thanks in advance

CCT