Multiple records and mailmerge's

[motormouth1991]

Hi all,

I hope you can help.

I'm abit of a novice at access, and am trying to set up a database. i will try and explain as best as i can what i have done, and what i would like the database to do.

Firstly i have two tables that are linked, one called tblBatch_Number, and the other is tblBatch_card.

tblBatch_number contains the following fields
Batch_Number (autonumber)
Date_printed (Date fieldset to date() to come up with todays date)
Batch_CardID (number field linked to the other table)

tblBatch_card has the following fields.
Batch_cardID (number related to each batchcard)
Batch_card (the description of the batch card)
Batch_weight (the weight of the batch card)

I have put this information into a form and i have an old code that the old IT guy used to use and im trying to figure out what he made it do.



There is two things i want the code to do..... Firstly i want it to ask the user how many batch cards they want to print, which it does, and then when i select a quantity it will set up that many new records. for instance if the autonumber was on 24 and i asked it to print 10 pages it would go to number print out 25-26-27 etc.. until it gets to 34.

at the moment on the code there is a .txt file that has the previous batch number on it so it knows what to grab, but this doesn't seem to be working, i will highlight where the dubug picks up the error.

The second thing i want it to do is each batch card has its own word document that needs to be printed with the information on it, so when one of the cards is selected it will mail merge the information to the right word document, so is there a way that the code could automatically do this, or even if the user has to navigate to the file.

I hope this is clear enough for you as its confusing me....

the code is below;

Sub Command4_Click()
'
' Macro1 Macro
'
Dim Message As String, Title As String, Default As String, NumCopies As Long
Dim I As Long
'set prompt
Message = "Enter the number of copies that you want to print"
'set title
Title = "Print"
'set default
Default = "1"
' Display message, title, and default value.
NumCopies = Val(InputBox(Message, Title, Default))
BatchNumber = System.PrivateProfileString("K:\****\****\****\Bat ch Number Master.txt", _
"MacroSettings", "BatchNumber")
If BatchNumber = "" Then
BatchNumber = 1
End If
Set Rng1 = ActiveDocument.Bookmarks("BatchNumber").Range
Counter = 0
While Counter < NumCopies
Rng1.Delete
Rng1.Text = BatchNumber
ActiveDocument.PrintOut
BatchNumber = BatchNumber + 1
Counter = Counter + 1
Wend
'save the next number back to the settings.txt file ready for the next use.
System.PrivateProfileString("K:\****\****\****\Bat ch Number Master.Txt", "MacroSetting", _
"BatchNumber") = BatchNumber



End Sub


Thanks in advance, i hope you can help.

[NoellaG]

Hi,

I think System.PrivateProfileString is a Word object function. You can check with Tools -> references if the word object library is loaded.
Or you can use the Scripting.FileSystemObject to access the textfile.

Apart from this, I wonder why you store this value in a text file, and not in an Access table? This would simplify getting the value.

greetings
Noëlla

[motormouth1991]

Hi Noella,

I have tried using the scripting.fileSystemObject, althought this is still flagging up saying that it is wrong, and i have not tired using this as a access table yest as i am trying to modify an old code which they used to use which worked up until a few months ago when it decided that it did not want to work...

I assume they had it in a text file so that multiple records could be added, can this still be done in a table format? for instance when it asks how many i want to print, it would add 10 new records.

Thankyou for your reply,

Regards

Ricky