How to run code on whole table and not just record displayed on form ?

[Gary]

I have a button on a form which has an onclick event which populates
fields on the current field using the me.controlname.value =
somevariable

I need to run this for every record in the database.

Is there a quick way of doing this, without opening the form, clicking
the next record, then clicking the button - then clicking the next
record, then clicking the next button etc...

the code i'm using is as follows...

Private Sub Command12_Click()

' new code...
' vartext(6) and vartext(7)
Dim incorporationdate As Integer
Dim countryoforigin As Integer
Dim typeofbusiness As Integer
Dim natureofbusiness As Integer

Dim referencedate As Integer
Dim accountsmadeupto As Integer
Dim accountsdue As Integer
Dim accountsreturn As Integer
Dim explanationofbusiness As Integer

varText = Split(copieddata, vbCrLf)
Me.Company_Name.Value = varText(0)
Me.address1.Value = varText(1)
Me.address2.Value = varText(2)
Me.address3.Value = varText(3)
Me.address4.Value = varText(4)

companynumber = varText(5) ' assign the fifth line to the
variable stringcompanynumber
companyinteger = Mid(companynumber, 13) ' assign the 'number' out of
stringcompanynumber to companynumber
Me.Company_Reg_No.Value = companyinteger ' insert companynumber
into 'company reg no' field.

For intloop = 6 To 10

incorporationdate = intloop + 1
countryoforigin = intloop + 3
typeofbusiness = intloop + 5
natureofbusiness = intloop + 6
explanationofbusiness = intloop + 7


If Len(Trim(varText(intloop) & "")) > 0 Then

Me.status.Value = Mid(varText(intloop), 9)
Me.Date_of_Incorporation.Value = Mid(varText(incorporationdate), 25)
Me.biztype.Value = varText(typeofbusiness)
Me.[SIC Explanation].Value = varText(explanationofbusiness)
Me.Refresh
End

' varText(intLoop) contains a value
Else
' varText(intLoop) doesn't contain a value
End If
Next intloop


Thanks for your help,

Gary-

 

[Jeff Boyce]

Gary

See questions/comment in-line below:

I have a button on a form which has an onclick event which populates
fields on the current field using the me.controlname.value =
somevariable

"populates fields on the current field" -- I don't understand what you mean.

"me.controlname" -- the '.' operator is intended to represent a Method.
While this may work, you may wish to use the "!" operator, used to show
user-created objects, such as a control on a form.

I need to run this for every record in the database.

How can you fill one field with every record's value? If your database is
well-normalized, you'd only use one value per field.

Is there a quick way of doing this, without opening the form, clicking
the next record, then clicking the button - then clicking the next
record, then clicking the next button etc...

"doing this" still isn't clear to me. Could you describe a bit more about
where you are starting (i.e., what data structure you already have), and
where you want to get to (a final product/dataset/...?)?

the code i'm using is as follows...

Private Sub Command12_Click()

' new code...
' vartext(6) and vartext(7)
Dim incorporationdate As Integer
Dim countryoforigin As Integer
Dim typeofbusiness As Integer
Dim natureofbusiness As Integer

Dim referencedate As Integer
Dim accountsmadeupto As Integer
Dim accountsdue As Integer
Dim accountsreturn As Integer
Dim explanationofbusiness As Integer

varText = Split(copieddata, vbCrLf)

You hadn't mentioned anything about "copieddata" before it shows up in your
code. Where is this coming from? Where do you want it to go?

Me.Company_Name.Value = varText(0)
Me.address1.Value = varText(1)
Me.address2.Value = varText(2)
Me.address3.Value = varText(3)
Me.address4.Value = varText(4)

companynumber = varText(5) ' assign the fifth line to the
variable stringcompanynumber
companyinteger = Mid(companynumber, 13) ' assign the 'number' out of
stringcompanynumber to companynumber
Me.Company_Reg_No.Value = companyinteger ' insert companynumber
into 'company reg no' field.

For intloop = 6 To 10

incorporationdate = intloop + 1
countryoforigin = intloop + 3
typeofbusiness = intloop + 5
natureofbusiness = intloop + 6
explanationofbusiness = intloop + 7

You are adding integer values to these fields?

If Len(Trim(varText(intloop) & "")) > 0 Then

?!You are testing the text properties of something named "intloop", and that
appears to be an integer value?! Also, you don't appear to have Dim'd this
variable -- if you don't have Option Explicit set, do it!

Me.status.Value = Mid(varText(intloop), 9)

Ditto -- ?string characteristics of an integer?!

Me.Date_of_Incorporation.Value = Mid(varText(incorporationdate), 25)
Me.biztype.Value = varText(typeofbusiness)
Me.[SIC Explanation].Value = varText(explanationofbusiness)
Me.Refresh

This is using the "Refresh" Method on the Form in which this is running. Is
that what you were trying to do?

End

?End what?

' varText(intLoop) contains a value
Else
' varText(intLoop) doesn't contain a value
End If
Next intloop


Thanks for your help,

Gary-

I really don't get what you're trying to do. More info, please...

Jeff Boyce
<Office/Access MVP>

 

[Gary]

"populates fields on the current field" -- I don't understand what you
mean.
Sorry that should have read on the current form. I'll outline the
situation in full to explain...

I have data in the clipboard. On my form I paste the data into the text
box control, copieddata. This data comprises a company name,company
address, company number and other information. Because each line of
information on the clipboard ends with a CRLF I am able to use SPLIT to
seperate the contents of this textbox into distinct lines. This is
important because I need to move the data from this control into
discrete fields. I particularly want to populate the company name field
with an 'extracted' company name from the copieddata text box. I also
want to extract the address lines into address line fields. And finally
I want to extract the company number into a company number field.
Having used SPLIT, I am able to test the data from the control
copieddata one line at a time and depending on my evaluation of each
particular line, put that line of data into it's relevent field.

I have 6 text boxes on my form. Each takes care of a particular unit of
data from the copieddata control. Four of the text boxes are labelled
address 1, address 2, address 3, and address 4 respectively. The fith
is labelled company name, and the sixth is labelled company number.


How can you fill one field with every record's value?
If your database is well-normalized, you'd only use one value per
field.
- Allow me to explain what I meant.

The value for each of the mentioned six fields is different for each
record. The actual value of each field, for each record, is determined
by looking at the field copieddata which contains a chunk of data and
is unique for each record. On my form when I click the extract data
button, the code I have included in my previous post is executed. For
the current record that the form is displaying - the correct data is
extracted from the control copieddata and this data is broken down and
used to populate the corresponding fields on the form which relate to
the record which the form is currently displaying.

Now I have 1000 records. For each of these records I need to extract
data from the copieddata control and place it, in the correct field. At
the moment the only way I can do this is to open my form at record one,
click the extract data button, then click the next record selector
button, click the extract data button, then click the next record
selector button ... ad infinitum...

The code behind the button works perfectly. All I am trying to do is
run it for every record, and not just the one record I currently have
displayed on my form.

I hope this makes a bit more sense.

Gary -

 

[Jeff Boyce]

Gary

Let's see if I can paraphrase your situation accurately:

You have 1000 records (all run together, perhaps as a comma-separated text
file) that you want to import into Access and place, after parsing, in
fields.

If so, you don't need to use a form at all.

You could:

Import the raw data into a (temporary) table
Create a query that parses the data into the fields you want
Convert that query into an append query, and append to your (permanent)

table.

 

[Gary]

No that's not what i've got Jeff..

I have 1000 records in an access database. Every field for every record
is blank with the exception of one field. Copieddata. This field
contains a chunk of data unique to each record. This data consists of
lines of data seperated by CRLF.

I am seeking to seperate this data into fields for each record.

Many Thanks,
Gary-

 

[TedMi]

Gary: Ok, after your interchange w/Jeff I think I understand what you are
trying to do:
Take the value out of one field of a record, split it into several
substrings, and place those into other fields of the same record; repeat for
every record. Is that correct?
If so, you don't use a form. The purpose of a form is to provide a user
interface for manual manipulating of records, for most operations limited to
doing so one record at a time. What you are trying to do requires NO user
interaction, so don't use a form.
Look up how to create and manipulate recordsets, whose purpose in life is to
allow iterative processing under programmatic, not manual, control.

 

[John Gray]

In other words, you can loop through a recordset (step through,
one record at a time) to achieve what you want. Take the code
that's behind your form button and put it in the loop, so that it
processes each record.

Like this...

Dim db As DAO.Database
Dim MyTable As DAO.Recordset

Set db = CurrentDb
Set MyTable = db.OpenRecordset("MyTableName", dbOpenDynaset)

MyTable.MoveFirst
Do While Not MyTable.EOF
' Do stuff here
' This will affect each record
MyTable.MoveNext
Loop

MyTable.Close
Set MyTable = Nothing


The VBA help file has explanations for Database, Recordset, EOF, MoveFirst, etc.

John


-----------------------------



...

[...]

What you are trying to do requires NO user interaction,
so don't use a form.
Look up how to create and manipulate recordsets,
whose purpose in life is to allow iterative processing
under programmatic, not manual, control.

[...]

 

[John Vinson]

No that's not what i've got Jeff..

I have 1000 records in an access database. Every field for every record
is blank with the exception of one field. Copieddata. This field
contains a chunk of data unique to each record. This data consists of
lines of data seperated by CRLF.

I am seeking to seperate this data into fields for each record.

Many Thanks,
Gary-

In that case you must run an Update query, probably with a VBA
function to parse this CopiedData field. This parsing won't be hard
(using the Split function) *IF* you can make the - perhaps
overoptimistic - assumption that *every single record* has a properly
formatted CopiedData field with exactly the same number of "subfields"
in exactly the same order.

If that's the case you can run an Update query updating each null
field to

Split([CopiedData], ",")(0)

using subscripts 0, 1, 2, 3... for the first, second, third, fourth...
comma separated strings in the field.

John W. Vinson[MVP]