Replies

I thought just by adding *this snip* at the line of: WordBasic.ToolsProtectDocument *NoReset:=1, Type:=2* would hold data from erasing when locking/unlocking the fields.

What’s the modern equivalent?
Speaking of updating archaic procedures…
Looking at the code of these things makes me wonder why every sub is called MAIN. What’s that about?

While most of those forms are indeed the dreaded, ‘clumsy’ unprotect-protect version, I am trying to get the GUI stuff implemented.
Most of our macros are based on Wordbasic if that helps you evaluate the situation any;-)

The current procedure has the customer save the file with the tags already in place that Pagemaker uses to set the styles.
I wanted to implement the UserForm and concatenate the styles to the textbox data, so that the final product would be the tags with the data e.g.
John Doe
East

1313 Mockingbird Lane
{et al} There are a total of 9 lines available, but that varies with the data supplied.

The ultimate goal would be for this data to somehow get into a database, which is easily done.
The dream is for it to create a grid of 12 cards, landscape format, 3 across and 4 down. The order is determined by the number of times an impression is needed. Each impression = 200 cards. If a customer needed 600 cards, the grid would have 3 cards on the grid or 1 for each 200 prints.

This is going to a 4 color professional printing press, so printing to spec is crucial. PageMaker has that kind of ‘discipline’ (i.e. leading and kerning) and Word doesn’t support 4 color separation.
Hence, my query about MSPublisher.
Thanks

The data is being entered via A Word UserForm. There are several variants of the UserForm, which is intended to ‘guide’ the User down the path of entering correct data, such as an email fork in the program which reveals a form that has an email address in a specific place, and has only a text box for their name so that’s the only part they can mess up in the email addie.

There will only be one form per user for data entry.

The current procedure is to save the files as a text file and title it with their name. I would like to automate the procedure and just have them fill in the ‘Save as’ and save in box.
There will also be several additional fields that hopefully will assist in the process of the final output
-Getting this data into a professional printing package like Pagemaker.
Still looking into that one…Know anything about Publisher?

I could use help on that too since I am a novice at this.

Sorry, tbl_Final was a last minute name change.
data1 was referring to tbl_Company data2 was sourcing tbl_Final. I posted a piece of old code. tbl_Final is the table I wish to write to.

CoNum is a numeric value.
CompanyID is a string.
RowID is the number I wish to increment when adding new records.
I had a feeling that somehow those set statements were contradicting each other.

The ‘program’ was designed in VB6 using data controls in a UserForm for the different tables. I had to modify the code by omitting the data controls to use it in Access97.
Data 1 was replaced by rs1 etc..

The statement itself calls table1 and counts the number of Companies. It then opens the larger table, compares the value of CoNum. If it’s there it does a record count and determines the highest value. Then it adds records as descibed in the For x statement below. It loops through every record of Table1 and runs the For x statement.

The data controls were used to concatenate the values of both ‘datacontrol results’ and create the string. Here is the entire code from the form.

Private Sub Command1_Click()
Dim i As Integer, x As Integer, z As Integer, itargetnum As Integer
Dim rs1 As Recordset, rs2 As Recordset
Dim db As Database
Dim lrecCount As Long, ldata1count As Long
Dim sql As String
Dim cCoName As String
sql = “select * from tbl_all where conum = ”
‘tbl_company
Data1.Recordset.MoveFirst
ldata1count = Data1.Recordset.RecordCount
Data1.Refresh
Data1.Recordset.MoveFirst
Data2.Recordset.MoveFirst

lrecCount = Data2.Recordset.RecordCount

Do While Not Data1.Recordset.EOF()

z = Data1.Recordset.Fields(“CoNUM”)

Data2.RecordSource = sql & Str(z)
‘This is where it concatenates the values, places it into ‘the table I want to write to, and increments the field ‘number by 1. This is called later in the x statement ‘below.

Data2.Refresh

lrecCount = Data2.Recordset.RecordCount + 1

cCoName = Data1.Recordset.Fields(“CompanyID”)

For x = lrecCount To 38
Data2.Recordset.AddNew
Data2.Recordset.Fields(“Conum”) = z
Data2.Recordset.Fields(“SurveyNo”) = “5002001”
Data2.Recordset.Fields(“CompanyID”) = cCoName
Data2.Recordset.Fields(“rowid”) = x
Data2.Recordset.Update
Next x

Data1.Recordset.MoveNext

Loop

End

End Sub

Sorry, still Word97 here.
How is this done in the ‘dark ages’ if it can be done…and I’m relatively sure that it can. I am even more certain that it is 100 times more difficult.
Thanks

Sorry, that didn’t work for me.

‘No current record’ error was detected at the rs2.MoveLast line

Edited by jazman2001 on 01/02/23 23:21.

Just had a friend show me how remarkably easy it was with VB. Worked like a charm. Naturally, my approach was all wrong as far as loop diagrams go. It even took me an hour to explain what I actually wanted to him, so I apologize to you all for bogging you down in this tedious process and my confusing submissions.
Thanks for all of your help.

I created a basic script that allows me to add records and fill values, but I’m still fuzzy on the concept of getting the row number then increasing the value by 1 till I hit 38. Going beyond 38 is fine, but I don’t need duplicate RowIDs for existing records.

I went from Acccess 97(work) to 2k(home) and I was getting tripped up in the object variables. I read that thread pertaining to the different libraries. Thanks for that one.

Public Sub AddRec()
Dim db As Database
Dim rs As Recordset
Dim CoNum As Integer
Dim CompanyID As String
Dim RowID As Long

Set db = CurrentDb
Set rs = db.OpenRecordset(“tbl_All”)

For CoNum = 1 To 4
For RowID = 1 To 38
Here’s where I need to grab the existing value for each CoNum from RowID and step 1 to 38-or at the very least add 38 from that value
‘If rs![ID] = Null Then
rs.AddNew
rs![SurveyNo] = “5002001”
rs![CoNum] = CoNum
rs![RowID] = RowID
rs.Update
‘End If
Next
Next
End Sub

Does this place have a ‘bunny slopes’ for us beginning programmers?

Thanks again

Actually, that was precisely what I needed.
Well, envisioning it your way helped me to realize that I use the DISTINCT to make it’s own table and index the entries.
Create a relationship- then a new make-table query.
This ties the company number down…then just use the simple script to add new records.

Thanks for the push!! G-man

In design view of the report,
View/ Sorting/Grouping
Drag the field you want to sort on first to the top of the window. If all of your data is correct…and THAT’s a HUGE IF…you won’t see any blanks or funky characters when you preview your labels.

Ex: If you have a field for “city” and you place city as the priority (by dragging the row to the top of the window in [designview/View/Sorting Grouping] it will sort by city.
If you see blanks or funky characters, open the table, select the column and hit the a-z sort button.

Warning: Always save a copy of your table before you edit your existing data. That’s always rule 1. ALWAYS!!
This happens to EVERY beginner

It is a database table that I exported to Excel for easier viewing.
The Field names are ID-SurveyNo-CompanyID-RowID-Company-Location
The row under that just lists the datatypes for each (hence, the italics)

The customer wants a table that has 38 placeholder records for each Customer that may be filled in/updated later. Apparently, they don’t want to redo the ASP code that is tripping up the ‘reponse’form.

I wish I knew more code. It seems so simple…figuratively.

Use a Distinct query to get each indexed listing in Field3 write it to a varA…go to the table which holds the records (or another query which uses this value to retrieve all records fitting this criteria) Use varA and find the highest value in Field4 which will be varB…add new records filling in Field2 with a simple string value and loop until varB < 39. Start over with the next index listing from the Distinct query and do it again until each listing in the distinct query has been used.

Unfortunately, explaining why I want to do this has been the hardest part for this forum to digest, while placing my own pseudocode has done a great diservice to the entire thread. I think that was my biggest folly.

Thanks for your patience

The existing data is already in it’s own table. The 38 records [per companyID] will be added now as placeholder records. It will eventually be exported to a tab-delimited text file and re-inserted. There will be no more than 38 records for each CompanyID [Field3]. Ever.

Apparently, they want these placeholder records so that the customer can just fill in the blank ones and just update the table.

The ASP code using this table requires this many records to successfully run the script. Anything less, trips the code. So each group must have 38 records to even meet the criteria of the code.