Storing Text Box Data in Separate Table

  • Thread starter Thread starter Chris C.
  • Start date Start date
C

Chris C.

Hey everybody,

I have a form that has a single-table record source. This table is
named [Single Well Data]. What I want to do is store the value of one
of my text boxes, [wellname], into a separate table. It should be
stored in the table [newtable] in the field [Well Name]. I can't seem
to get this to work, and am wondering if anyone would be willing to
help out. I think this can be done with VB, but not sure. Your help
is greatly appreciated.

Thanks,
Chris C.
 
Here's the query you would use:

Docmd.RunSql "Insert Into NewTable([WellName]) Values('" & Me.WellName
& "');"

Now in your form, you need to decide where you want this statement to
be executed and put it there.
 
Hey thanks for helping out Jeff! I tried what you suggested, and put
the statement in the OnOpen event of my form. But unfortunately, it
doesn't work. In the VB editor, the statement has an error and says
"Expected: end of statement"

Thanks,
-Chris
 
Does the statement fit onto one line when you paste it in? If not
either put an underscore at the end of the first line or at the end of
the first line hit delete until it's all on one line. I tested the
line I gave you and it should work.
 
Hey thanks Jeff, it does work!! I don't know what I did before, but I
just tried copying and pasting your code in and moved it to one line,
and it worked. You are amazing!!

Thanks,
Chris C.
 
I have another question that you might be able to answer. What if I
had another text box named [API #] and I wanted to store it in the same
table record under the column [API#]? How would I re-write your code
snippet in order to accomadate both?

Thanks,
Chris C.
 
Docmd.RunSql "Insert Into NewTable([WellName], [API#) " & _
Values('" & Me.WellName & "', '" & Me.[API #] & "');"

I put this on two lines so it's easier to read. Make sure your second
line starts after the underscore.
 
That also worked. Thank you very much. I tried adding more than two
fields and your method worked but when I tried to add a field that was
a "Number" type instead of a "Text" type it came back with an error
that said

"Argument: not optional"

I'm not sure what this means, could you explain this. Also could you
explain why we need the & signs in your code example?

Thanks!
-Chris C.
 
You use the & when you are concatenating (pasting in a sequence) items
together. So if you had Field1="ABC" and Field2="DEF" and wanted to
put them together, your syntax would be Field1 & Field2. When writing
SQL and pasting it together like in what I wrote for you, you need to
make sure that you are enclosing string values in single quotes and no
quotes for numeric values. As far as your error, you may have
something wrong with your syntax. If you'd like to paste it here, I
can look at it for you. I hope I didn't confuse you too much.
 
Jeff said:
You use the & when you are concatenating (pasting in a sequence) items
together. So if you had Field1="ABC" and Field2="DEF" and wanted to
put them together, your syntax would be Field1 & Field2. When writing
SQL and pasting it together like in what I wrote for you, you need to
make sure that you are enclosing string values in single quotes and no
quotes for numeric values. As far as your error, you may have
something wrong with your syntax. If you'd like to paste it here, I
can look at it for you. I hope I didn't confuse you too much.
Click to expand...

No, you didn't cause any confusion at all. That was very helpful.
Thank you very much! As far as the error, I just decided to to leave
that field out. I decided that I really didn't need it. But thanks
for offering to help anyway.

Thanks Again!
Chris C.
 
Back
Top