You have values from a Main and Subform that you want to enter to a table?
Do you want to enter all three values to the same table, or the one from the
mainform to one table, and two from the subform to a seperate table?
The reason I ask is because in a standard Main and Subform setup, the
subform contains child records that are held in a child table (on the Many
side of a one to many relationship). While it is certainly possible to take
a value from the main and two values from the sub and insert them into a
single table, I feel the need to question the table design. You may though,
have a less than normal scenario where you keep data from the same table on a
mainform and a subform. In any case, if you are not familiar with the term
Normalization (referring to the correct table structures of a database), you
would do yourself well to research the topic before proceeding.
Also, depending on your form setup, you may be able to bind these textboxes
with some query work to avoid having to do this through code. Although,
possibly these are already bound to a table(s) and you are adding them for
some sort of archiving (if you are duplicating these stored values in a
table, note that it is a bad practice to store data more than one place in a
db... use queries to pull data where you need from it's orginal location
rather than duplicating the stored values somewhere else).
That said, here's some pointers on how to do as you ask (click a button from
a mainform and have it insert one value from the main, and two values from
the sub into a single table).
First, how to get a value from a subform control...
Dim x As String/Long?
x = Me!SubformControlName.Form!textboxA
Make sure SubformControlName is the name of the CONTROL that holds the
subform, and not the name of the Form that is the subform. This will
retrieve the value of the control (not handling NULL values).
Next, putting string values inside a string.... you need double quotes ("")
inside a string to denote a single quote ("). See below...
Dim strSQL As String
strSQL = "SELECT * FROM table WHERE StringVal = ""thisval"""
Debug.Print strSQL returns...
SELECT * FROM table WHERE StringVal = "thisval"
This is how you need to build the INSERT INTO sql string that you will use
in code... you need to make sure that if the values are string (text, not
numeric) then they are enclosed in double quotes. Also note, for Dates, you
would do the same, except with # instead of double quotes, so...
Dim strSQL as String
strSQL = "SELECT * FROM table WHERE DateField = #12/12/10#"
Numeric values do not need this. Here's some examples how to insert values
into a string when the values are in controls... you must exit the string,
contencate the control value, then close the string where required...
Dim strWhere As String
strWhere = "ThisNumber = " & Me.NumberControl
strWhere = "ThisDate = #" & Me.DateControl & "#"
strWhere = "ThisString = """ & Me.StringControl & """"
Next, you want to put the code behind a button on the mainform. I don't
know what version you're using, but in 2003 and before, open the properties
page of the control, click the Events tab, and double click in the list for
OnClick (or click the ... button on the side and choose Build Code). Now you
will have an event procedure in the vba module behind your form. It will
look like this:
Private Sub ButtonName_Click()
End Sub
Also note that a Line Continuation in the VBA code is a space followed by an
underscore... this makes for easy reading of code and, as far as the editor
is concerned, it's all in the same line.
Disregarding any error handling, and assuming using CurrentDb.Execute to run
the query, here is what your code would look like to insert three textbox
values (that hold strings) into a single table...
Private Sub ButtonName_Click()
Dim strSQL 'variable to hold the sql string
'build the string
strSQL = "INSERT INTO TableName " & _
"(Fieldname1, Fieldname2, Fieldname3) " & _
"VALUES (""" Me.textbox9 & """, """ & _
Me!SubformControlName.Form!textboxA & """, """ & _
Me!SubformControlName.Form!textboxB & """)"
'print the sql string to the immediate window (ctl+G to see immediate
window)
Debug.Print strSQL
'execute the query and tell us if there's an error executing it
CurrentDb.Execute strSQL, dbFailOnError
End Sub
In the above query, Fieldname1 would receive the value of textbox9,
fieldname2 would receive textboxA and fieldname3 would receive the value of
textboxB.
Assuming the values of textboxes 9, A and B are respectively: text9value,
textAvalue and textBvalue, the Debug.Print line of the code should print this
to your immediate window:
INSERT INTO TableName (Fieldname1, Fieldname2, Fieldname3) VALUES
("text9value", "textAvalue", "textBvalue")
Be extra careful of correct quote placement, spacing and commas when
building an sql string like this. You will get a Missing Operator or Invalid
Syntax error when the query tries to execute if there is any discrepancy in
the sql string.
This may be a bit more than you were bargaining for, but that's how to do it.
good luck! hth
--
Jack Leach
www.tristatemachine.com
"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
