How to add new records in Access using VBA code?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database with two tables - "Contacts" and "Notes". Each table is
keyed with an ID field (Contact ID & Notes ID) that is an Autonumber field.
We currently have 271 contacts and plan on adding more this week. Every time
our company sends a mass mailing, I want to bypass having to enter the notes
for each contact manually as this could take forever. I created a user form
as a dialog box that asks the user for the date of the mailing and any notes
that should be entered with the mailing. The resulting table should look
like this after two mailings:

Notes ID Contact ID Date Notes
1 1 3/31/05 Special Offer Postcard - 1
hour free
2 2 3/31/05 Special Offer Postcard - 1
hour free
3 3 3/31/05 Special Offer Postcard - 1
hour free
....
272 1 4/3/05 Informational Postcard -
Competitive Edge
273 2 4/3/05 Informational Postcard -
Competitive Edge
274 3 4/3/05 Informational Postcard -
Competitive Edge

How can I automatically add a new record for each contact ID and then input
the information given by the user as to the date and notes? Is this possible?

Thanks!
 
Here is the code I wrote to get the user info as strings for the date and
notes fields. Will this do me any good as far as using these strings to
populate data records? (The final message box was just so that I would know
I was getting the strings correctly. I'm pretty new to this. Thanks!)

Public stDate As String
Public stNotes As String

Option Compare Database



Private Sub MassMailDate_AfterUpdate()

stDate = MassMailDate.Value

End Sub

Private Sub MassMailNotes_AfterUpdate()

stNotes = MassMailNotes.Value

End Sub

Private Sub MMCancel_Click()

DoCmd.Close acForm, "MassMailForm"

Exit Sub

End Sub

Private Sub MMClear_Click()

MassMailDate.Value = ""
MassMailNotes.Value = ""
stDate = ""
stNotes = ""

Exit Sub

End Sub

Private Sub MMOK_Click()

Check_Date:
If stDate = "" Then GoTo Date_Msg_Box

Check_Notes:
If stNotes = "" Then GoTo Notes_Msg_Box

Msg_Box:
Dim Msg, Style, Title, Response
Msg = "You have entered " & stDate & " & " & stNotes & "."
Style = vbOKOnly
Title = "You're Done!"

Response = MsgBox(Msg, Style, Title)
If Response = "OK" Then GoTo Exit_MMOK_Click


Exit_MMOK_Click:
Exit Sub

Date_Msg_Box:

Dim Msg2, Style2, Title2, Response2
Msg2 = "You have not entered a date!"
Style2 = vbOKOnly
Title2 = "Enter Date"

Response2 = MsgBox(Msg2, Style2, Title2)
If Response2 = "OK" Then GoTo Exit_MMOK_Click

Notes_Msg_Box:
Dim Msg3, Style3, Title3, Response3
Msg3 = "You have not entered any notes!"
Style3 = vbOKOnly
Title3 = "Enter Notes"

Response3 = MsgBox(Msg3, Style3, Title3)
If Response3 = "OK" Then GoTo Exit_MMOK_Click

End Sub
 
If you just want to add records to the Notes table, you could use an append
query that you run from the database window.

The query's SQL would be something like this:

INSERT INTO Notes ( [Contact ID], [Date], Notes )
SELECT Contacts.[Contact ID], Date() AS TodayDate,
InputBox("Enter the notes:") AS TheNotes
FROM Contacts;

This query will prompt you for the notes string that you want to enter.

If you plan to do this with some data validation (was a string entered for
Notes, etc.), then you should use a form with textbox on it to allow entry
of the notes. Then (assuming that the form is named frmGetInfo and the notes
textbox is named txtNotes), you could run the query from this form (via a
command button). Note that the query would be changed to this:

INSERT INTO Notes ( [Contact ID], [Date], Notes )
SELECT Contacts.[Contact ID], Date() AS TodayDate,
Forms!frmGetInfo!txtNotes AS TheNotes
FROM Contacts;


Also, do not use Date as the name of a table's or query's field. It and many
other words are reserved words in ACCESS, and can create serious confusion
for ACCESS and Jet. See these Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


--

Ken Snell
<MS ACCESS MVP>

"Tonya -- www.yvonneandtonya.com" <Tonya --
[email protected]> wrote in message
 
Thanks! I will try that tomorrow when I am thinking clearly. One more
question, though -

I want to be able to enter this info a few days after the date of the
mailing, so don't want to use TodayDate. If the textboxes are on the same
form, could I change the wording of this line:

SELECT Contacts.[Contact ID], Forms!frmGetInfo!txtDate AS TheDate,
Forms!frmGetInfo!txtNotes AS TheNotes

Thanks again for your help!!!
 
Back
Top