Data entry VBA

  • Thread starter Thread starter haas.chaudhry
  • Start date Start date
H

haas.chaudhry

OK, to avoid confusion, all i want is the code for a procedure where if I press a button on the form it will call up a VBA procedure which would essentially update/add a frecord to the table. I KNOW THIS CAN BE DONE WITHOUT CODE, but I'm looking for VBA code for other purposes:

I have a table called tblContacts
There are two fields in this table: CompanyName and PhoneNumber

I created a form where the user can see if a company name exists through a Combo Box with the source being the list of entries in the tblContacts field - if it doesn't, he enters a new company into the a txtCompanyName box and a phone number into the txtPhoneNumber box on the form. Next, user presses a button on the form which would call a VBA procedure which runs and takes the values from txtCompanyName and txtPhoneNumber and adds to the tblContacts table

I know that you can do this easily through Form Wizard OR a RECORDSOURCE (thanks Larry), but I wanted to know how to program this into a VBA procedure.. This is a simple example to learn the technique so I cna apply it to one with multiple fields, etc.

Thank you for your help in advance!
 
OK, to avoid confusion, all i want is the code for a procedure where if I press a
button on the form it will call up a VBA procedure which would essentially
update/add a frecord to the table. I KNOW THIS CAN BE DONE WITHOUT
CODE, but I'm looking for VBA code for other purposes:

I have a table called tblContacts
There are two fields in this table: CompanyName and PhoneNumber

I created a form where the user can see if a company name exists through
a Combo Box with the source being the list of entries in the tblContacts field
- if it doesn't, he enters a new company into the a txtCompanyName box
and a phone number into the txtPhoneNumber box on the form. Next,
user presses a button on the form which would call a VBA procedure
which runs and takes the values from txtCompanyName and txtPhoneNumber
and adds to the tblContacts table

I know that you can do this easily through Form Wizard OR a RECORDSOURCE
(thanks Larry), but I wanted to know how to program this into a VBA procedure.
This is a simple example to learn the technique so I cna apply it to one
with multiple fields, etc.
Click to expand...

Lot's of variables.
But here is general outline of one way to go about it...
(You can use the keywords as a basis for additional research.)

1) First capture the Text values of interest in variables.
Dim sCompName As String
Dim sPhoneNum As String

Not sure how or when you want to do this.

Second you need to decide on a data access layer, which will also be
dependent on what file format you are using. (mdb = Jet 4 or accdb =
ACE 12)

For this I'll use DAO 3.6 Library for a Jet 4 formatted MDB file (it
is the easiest. <g>)

2) Add leading and trailing ticks to your variables.
sCompName = "'" & sCompName & "'"
sPhoneNum = "'" & sPhoneNum & "'"
' this is so they will fit in your query below.

3) The core of the solution
Open datasebase. Build the query you want, in this case an Insert,
then execute it.

Dim db As DAO.Database
Dim strSQL As String

' watch for wrap
strSQL = "INSERT INTO tblContacts([CompanyName], [PhoneNumber])
VALUES (" & sCompName & ", " & sPhoneNum & ")"

Set db = OpenDatabase(<database pathname>)
db.Execute strSQL
db.Close
Set db = Nothing

4) You will need a different query for each type of action you want to
do.

Of course you could build your string with
... VALUES (" & "'" & sCompName & "', " & ... just as well <g>

You can build and test your queries in Access's query builder, then
copy and paste. Or you could call the query directly. (Like I said,
lot's of ways to skin that cat.)
By posting this brute force method, hopefully others will soon be
along to supply more elegant solutions. <g>

hth to give the general idea.
Note: VBA basics are pretty much identical accross all Office
products. Don't limit your research to MS Access sites only.

Also appreciate that when you go this route you are essentially
chewing on one file (objects/s) with two different tools - results may
not always be what you expected, and even subtlety different depending
on circumstances.

-ralph
 
Back
Top