Append From Form to Table

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

Guest

I have 5 unbound fields in my form that makes up the address. I want to be
able to check for duplicate addresses but if there is no duplicate, to append
the values in those unbound fields to the tblAddress table. I have been
trying to use the Design View in Access to accomplish but it just doesn't
happen. The tblAddress has an autonumber field which is the PK. How do I
accomplish something like this? Thanks.
ck
 
On the click on the button you can use this code

dim MyDB as database, MyRec as recordset
set MyDb=codedb
Set MyRec-MyDb.openrecordset("SELECT A, b, c, d, e FROM MyTable WHERE A='" &
me.Field1 & "' AND b='" & me.Field2 &"' AND c='"& me.Field3 &"' AND d='"&
me.Field4 &"' AND e='" & me.Field5 &"'")
If MyRec.eof then
Myrec.addnew
MyRec!A=Me.field1
MyRec!b=Me.field2
MyRec!c=Me.field3
MyRec!d=Me.field4
MyRec!e=Me.field5
MyRec.update
else
msgbox "Address exist"
endif

If the field is number then remove the single '
I hope it helps.
 
CK,

If you mean doing this from the Query Designer, then yes, it is
possible. Make it as an Append Query. In the Field row of the query
design grid, enter references to the controls on the form, for example:
[Forms]![NameOfForm]![NameOfControl]
.... and then enter the names of the fields in the Append To row.
The SQL view of such a query will end up looking something like this...
INSERT INTO YourTable ( Address1, Address2, Address3, Address4, Address5 )
SELECT [Forms]![YourForm]![Textbox1], [Forms]![YourForm]![Textbox2],
[Forms]![YourForm]![Textbox3], [Forms]![YourForm]![Textbox4],
[Forms]![YourForm]![Textbox5]

Or, if you want to do it in a VBA procedure...
CurrentDb.Execute "INSERT INTO YourTable" & _
" ( Address1, Address2, Address3, Address4, Address5 )" & _
" VALUES ( '" & Me.Textbox1 & "', '" & Me.Textbox2 & "', '" &
Me.Textbox3 & "', '" & Me.Textbox4 & "', '" & Me.Textbox5 & "' )"
 
Thanks, Ofer...that did the trick.
ck

Ofer said:
On the click on the button you can use this code

dim MyDB as database, MyRec as recordset
set MyDb=codedb
Set MyRec-MyDb.openrecordset("SELECT A, b, c, d, e FROM MyTable WHERE A='" &
me.Field1 & "' AND b='" & me.Field2 &"' AND c='"& me.Field3 &"' AND d='"&
me.Field4 &"' AND e='" & me.Field5 &"'")
If MyRec.eof then
Myrec.addnew
MyRec!A=Me.field1
MyRec!b=Me.field2
MyRec!c=Me.field3
MyRec!d=Me.field4
MyRec!e=Me.field5
MyRec.update
else
msgbox "Address exist"
endif

If the field is number then remove the single '
I hope it helps.
Click to expand...
 
Thanks, Steve for your help.
ck

Steve Schapel said:
CK,

If you mean doing this from the Query Designer, then yes, it is
possible. Make it as an Append Query. In the Field row of the query
design grid, enter references to the controls on the form, for example:
[Forms]![NameOfForm]![NameOfControl]
.... and then enter the names of the fields in the Append To row.
The SQL view of such a query will end up looking something like this...
INSERT INTO YourTable ( Address1, Address2, Address3, Address4, Address5 )
SELECT [Forms]![YourForm]![Textbox1], [Forms]![YourForm]![Textbox2],
[Forms]![YourForm]![Textbox3], [Forms]![YourForm]![Textbox4],
[Forms]![YourForm]![Textbox5]

Or, if you want to do it in a VBA procedure...
CurrentDb.Execute "INSERT INTO YourTable" & _
" ( Address1, Address2, Address3, Address4, Address5 )" & _
" VALUES ( '" & Me.Textbox1 & "', '" & Me.Textbox2 & "', '" &
Me.Textbox3 & "', '" & Me.Textbox4 & "', '" & Me.Textbox5 & "' )"

--
Steve Schapel, Microsoft Access MVP

I have 5 unbound fields in my form that makes up the address. I want to be
able to check for duplicate addresses but if there is no duplicate, to append
the values in those unbound fields to the tblAddress table. I have been
trying to use the Design View in Access to accomplish but it just doesn't
happen. The tblAddress has an autonumber field which is the PK. How do I
accomplish something like this? Thanks.
ck
Click to expand...
Click to expand...
 
Back
Top