By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,285 Members | 1,687 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,285 IT Pros & Developers. It's quick & easy.

VBA Code taking data from form entering into table

P: 31
I have 1 form, comprised of data from 2 tables. I am VBA coding to take the data and insert it into its repective table. My code isn't working and I am not sure. I know I am missing some code, but I am not sure what it is. Here is my code, can anyone please help me.


Private Sub Command32_Click()
On Error GoTo Err_Command32_Click
Dim strSQL1 As String
Dim strSQL2 As String
Dim strFinal As String
Dim FName As String
Dim LName As String
Dim ID As String
Dim Phone As String
Dim Email As String
Dim Comma As String

Dim qrydef As QueryDef
Dim qryName As String
qryName = "InputCaller"
QueryDefs.Delete qryName


Comma = "', '"
strSQL1 = "INSERT INTO Caller(C_F_Name, C_L_Name, Call_ID, Telephone, Email) VALUES('"
strSQL2 = "')"
FName = Request3.C_F_Name.Text()
LName = Request3.C_L_Name.Text()
ID = Request3.Call_ID.Text()
Phone = Request3.Telephone.Text()
Email = Request3.Email.Text()
strFinal = strSQL1& & FName& & Comma& & LName& & Comma& & ID& & Comma& & Phone& & Comma& & Email& & strSQL2& & ";"

Set qrydef = CreateQueryDef(qryName, strFinal)
DoCmd.RunSQL qrydef
DoCmd.GoToRecord , , acNewRec

Exit_Command32_Click:
Exit Sub

Err_Command32_Click:
MsgBox Err.Description
Resume Exit_Command32_Click

End Sub
Dec 1 '06 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,661
I'm not sure exactly what your situation is but the normal way of updating two tables from one form is to build the form bound to an updatable query connecting them both.
This way, any updates are done automatically by the form.
Dec 1 '06 #2

P: 31
I'm not sure exactly what your situation is but the normal way of updating two tables from one form is to build the form bound to an updatable query connecting them both.
This way, any updates are done automatically by the form.

Okay. That sounds good. We are novices, so how is that done?
Dec 1 '06 #3

NeoPa
Expert Mod 15k+
P: 31,661
1. Design tables
2. Design updatable query (test by running - if you get the empty item at the end of your list in which to enter a new record then it is updatable) linking both tables together. The Query Wizard can help you here.
3. Use the Form Wizard to create a form based on this query. Depending on the relationships assigned etc it will create a form with or without a subform for you.
Dec 1 '06 #4

NeoPa
Expert Mod 15k+
P: 31,661
If you are novices, I highly recommend taking some time to go through the links in (Links to useful sites). It will help you better understand what's being said in these forums.
You're unlikely to find answers here that do all your work for you. Instead you'll get pointers for larger questions and technical responses for technical questions.
I'm not sure if this is very clear but I am sure that you'll get more out of Access and this site if you familiarise yourselves with the content in there.
Dec 1 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.