My append query and I dont understand each other

[ThomasK via AccessMonster.com]

I have an append query that takes information from unbound textboxes on a
form and creates new records in a table. The problem Im having (and I suspect
that its because I dont know what Im doing) is that the query will create
several new records as opposed to just one. If my table has 10 records in it,
then the append query creates 10 new records. So the table ends up with 20
records. I just want to add one new record not several.

And before you ask, I dont want to use bound fields on the report, because I
dont want the record to be added to the table unless the user confirms it by
hitting a command button.

This is the query::
INSERT INTO tblAddresses ( StreetNumber, Direction, StreetName, Suite,
BuildingNumber )
SELECT [Forms]![QBF_Form]![WhatStreetNumber] AS [Street Number], [Forms]!
[QBF_Form]![WhatDirection] AS Direction, [Forms]![QBF_Form]![WhatStreetName]
AS [Street Name], [Forms]![QBF_Form]![WhatSuite] AS Suite, [Forms]![QBF_Form]!
[WhatBuildingNumber] AS [Building Number]
FROM tblAddresses;

Thanks for the help,

 

[Keith Wilby]

And before you ask, I dont want to use bound fields

That's a shame because using a bound form would be the easiest way. You can
always code the bound form to not commit the record until the button is
clicked.

Keith.
www.keithwilby.com

 

[ThomasK via AccessMonster.com]

Yes, but thats where we get to the underlying problem of "I dont know what Im
doing".. You see I dont know how the write code, so Im forced to use macros,
queries, and command buttons, when using code would be the right way.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Like this:

INSERT INTO tblAddresses ( StreetNumber, Direction, StreetName, Suite,
BuildingNumber )
VALUES ([Forms]![QBF_Form]![WhatStreetNumber] AS [Street Number],
[Forms]![QBF_Form]![WhatDirection] AS Direction,
[Forms]![QBF_Form]![WhatStreetName] AS [Street Name],
[Forms]![QBF_Form]![WhatSuite] AS Suite,
[Forms]![QBF_Form]![WhatBuildingNumber] AS [Building Number])

Use the VALUES keyword and you have to delimit the actual values (the
values in the form) with parentheses.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSRsN9oechKqOuFEgEQKkHQCgp8LUX3K2BUgqt9MC0Zv0tcCcq28AnAup
Awg/WrpwmtHMhXL5JxtGaCV1
=5hDf
-----END PGP SIGNATURE-----

 

[John Spencer]

Use the alternative syntax for inserting records

INSERT INTO tblAddresses ( StreetNumber, Direction, StreetName, Suite,
BuildingNumber )
Values([Forms]![QBF_Form]![WhatStreetNumber]
, [Forms]![QBF_Form]![WhatDirection]
, [Forms]![QBF_Form]![WhatStreetName]
, [Forms]![QBF_Form]![WhatSuite]
, [Forms]![QBF_Form]![WhatBuildingNumber])

Although doing it directly from a form would be a lot easier.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[ThomasK via AccessMonster.com]

Wow, thank you all for the help. All that Im trying to do is have a form
where the user types in an address, hits a button to check for similar
addresses, and then selects either the similar address or uses the original
address that they typed in. They would select the correct address and then
would be able to use that record to get to an associated (one to many) table
record entry form.

Use the alternative syntax for inserting records

INSERT INTO tblAddresses ( StreetNumber, Direction, StreetName, Suite,
BuildingNumber )
Values([Forms]![QBF_Form]![WhatStreetNumber]
, [Forms]![QBF_Form]![WhatDirection]
, [Forms]![QBF_Form]![WhatStreetName]
, [Forms]![QBF_Form]![WhatSuite]
, [Forms]![QBF_Form]![WhatBuildingNumber])

Although doing it directly from a form would be a lot easier.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have an append query that takes information from unbound textboxes on a
form and creates new records in a table. The problem Im having (and I suspect

[quoted text clipped - 17 lines]

Thanks for the help,

 

[Keith Wilby]

Yes, but thats where we get to the underlying problem of "I dont know what
Im
doing".. You see I dont know how the write code, so Im forced to use
macros,
queries, and command buttons, when using code would be the right way.

Here's some code that would probably do it in a bound form (untested). In
the form's Before Update event:

If MsgBox("Are you sure you want to commit this record?", vbYesNo,"Confirm
change") = vbNo Then
Cancel = True
Me.Undo
End If

That way the user would be prompted whenever thay tried to save the data,
whether it be by clicking byour command button, the record selector or by
navigating away from the current record.

Keith.
www.keithwilby.co.uk