how to add row in ms access database from ms word vba form

[Haroon]

hi

i would like to add a new row in ms access when the form loads from template
(autonew) as i want to get auto number from ms access field and display it in
textbox on the form, or is there any other way of generating auto number in a
textbox when the form loads?


many thanks.

 

[David Horowitz]

Hi Haroon,
Not sure I fully understand what you're looking for, but let's try.
Are you only using Access just for an Autonumber capability? If that's the
case, you don't need to do that just to generate an Autonumber in Word.
It sounds like what you need is a number that will retain its value over
time so you can keep incrementing it each time you create a new instance of
your document based on your template. If I got that right so far, then you
need some storage for your variable that will last.
There are multiple options. I might just store the value in the registry.
You could probably also use the CustomDocumentProperties collection on the
template but I don't have sample code for that.
To write to the registry using Word you could use a PrivateProfileString
call. You'd need to decide where in the registry your key belongs, maybe
under
HKLM\Software\YourCompany\YourProduct\Settings
If you go into Word VBA and type PrivateProfileString, then hit F1, or
search online, you can find out how to do this. Or post another question
here if you want to go that way and get stuck.

 

[Doug Robbins - Word MVP on news.microsoft.com]

See the last of the following series of articles

Please Fill Out This Form
Part 1: Create professional looking forms in Word
http://www.computorcompanion.com/LPMArticle.asp?ID=22

Part 2: Adding Automation to your Word forms.
http://www.computorcompanion.com/LPMArticle.asp?ID=46

Part 3: Learn more VBA (macros) to automate your forms.
http://www.computorcompanion.com/LPMArticle.asp?ID=119

Part 4: Use custom dialog boxes in your Word forms
http://www.computorcompanion.com/LPMArticle.asp?ID=127

Part 5: Connect your AutoForm to a database to save input time and keep
better records!
http://www.computorcompanion.com/LPMArticle.asp?ID=136


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Haroon]

Hi David,

Thanks for reply, bascially I have created template which loads up form
where users input data and click submit and data gets filled on document, i
would like to auto generate a number, e.g. Ref, to show up on the form
instead of user manually writing the number in Ref field, e.g 1, 2, 3, etc.
So when I load the template, the Ref number goes 1 up from previous number,
e.g. if previous Ref was 2, next one would be 3.

hope you understand now.

 

[Doug Robbins - Word MVP on news.microsoft.com]

See the article "Creating sequentially numbered documents (such as
invoices)" at:

http://www.word.mvps.org/FAQs/MacrosVBA/NumberDocs.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Haroon]

thanks Doug,

thats what i was looking for, but how do i get the number to display it in a
textbox on a form?

thanks.

 

[Haroon]

SORTED!
this is the code i made to show the incrementing numbers in the textbox on a
form, so each time form loads up, the number goes up by 1.

---------------------------------------------------------------------------------
order = System.PrivateProfileString("C:\Settings.Txt", _
"MacroSettings", "Order")

If order = "" Then
order = 1
Else
order = order + 1
End If

System.PrivateProfileString("C:\Settings.txt", "MacroSettings", _
"Order") = order

TextBox1.Text = Format(order, "00#")

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