S
Slez via AccessMonster.com
I'm posting this in the Queries section but ultimately will be using the SQL
directly in a form's code. I'm not quite sure how to accomplish this and am
looking for some help. Here is what I'm trying to accomplish:
I want to enable users to open frmCopyBid which has 3 fields: ProjectID &
ExistingBidNumber, that have data already contained because of their record
source, and DestinationBidNumber, where users enter the destination record.
As an example: ProjectID may be 19 and ExistingBidNumber may be 1, and users
want to copy the related records for that into BidNumber 5, which they have
entered into DestinationBidNumber. Once that is filled in, the click
cmdAcceptCopyBid to carry out the append query.
Mainly, what I don't know how to do is reference DestinationBidNumber within
the SQL so that it knows where to place the selected records. You will see
my "AS" statement in the code below, which I believe is incorrect. When I
try to run the SQL now, I get the following error: Run-time error '3075':
Syntax error (missing operator) in query expression 'Item.ProductSummary '(
[Forms]![frmCopyBid]![DestinationBidNumber])". Debugging results in the
"DoCmd.RunSQL strItemSQL" being highlighted.
Here is my Code/SQL:
Private Sub cmdAcceptCopyBid_Click()
Dim strItemSQL As String
strItemSQL = "INSERT INTO Item ( ProjectID, BidNumber, RoomNumber,
ItemNumber, RoomName, ElevationReference, ProductSummary )"
strItemSQL = strItemSQL + " SELECT Bid.ProjectID, Bid.BidNumber, Item.
RoomNumber, Item.ItemNumber, Item.RoomName, Item.ElevationReference, Item.
ProductSummary "
strItemSQL = strItemSQL + " '([Forms]![frmCopyBid]![DestinationBidNumber])
' AS BidNumber"
strItemSQL = strItemSQL + " FROM Bid INNER JOIN Item ON (Bid.BidNumber =
Item.BidNumber) AND (Bid.ProjectID = Item.ProjectID) "
strItemSQL = strItemSQL + " WHERE (((Bid.ProjectID)=[Forms]![frmCopyBid]!
[ProjectID]) AND ((Bid.BidNumber)=[Forms]![frmCopyBid]![ExistingBidNumber]))
;"
DoCmd.RunSQL strItemSQL
End Sub
I really appreciate any assistance with this! I have used this forum alot
and have learned a great deal!
Thans
Slez
directly in a form's code. I'm not quite sure how to accomplish this and am
looking for some help. Here is what I'm trying to accomplish:
I want to enable users to open frmCopyBid which has 3 fields: ProjectID &
ExistingBidNumber, that have data already contained because of their record
source, and DestinationBidNumber, where users enter the destination record.
As an example: ProjectID may be 19 and ExistingBidNumber may be 1, and users
want to copy the related records for that into BidNumber 5, which they have
entered into DestinationBidNumber. Once that is filled in, the click
cmdAcceptCopyBid to carry out the append query.
Mainly, what I don't know how to do is reference DestinationBidNumber within
the SQL so that it knows where to place the selected records. You will see
my "AS" statement in the code below, which I believe is incorrect. When I
try to run the SQL now, I get the following error: Run-time error '3075':
Syntax error (missing operator) in query expression 'Item.ProductSummary '(
[Forms]![frmCopyBid]![DestinationBidNumber])". Debugging results in the
"DoCmd.RunSQL strItemSQL" being highlighted.
Here is my Code/SQL:
Private Sub cmdAcceptCopyBid_Click()
Dim strItemSQL As String
strItemSQL = "INSERT INTO Item ( ProjectID, BidNumber, RoomNumber,
ItemNumber, RoomName, ElevationReference, ProductSummary )"
strItemSQL = strItemSQL + " SELECT Bid.ProjectID, Bid.BidNumber, Item.
RoomNumber, Item.ItemNumber, Item.RoomName, Item.ElevationReference, Item.
ProductSummary "
strItemSQL = strItemSQL + " '([Forms]![frmCopyBid]![DestinationBidNumber])
' AS BidNumber"
strItemSQL = strItemSQL + " FROM Bid INNER JOIN Item ON (Bid.BidNumber =
Item.BidNumber) AND (Bid.ProjectID = Item.ProjectID) "
strItemSQL = strItemSQL + " WHERE (((Bid.ProjectID)=[Forms]![frmCopyBid]!
[ProjectID]) AND ((Bid.BidNumber)=[Forms]![frmCopyBid]![ExistingBidNumber]))
;"
DoCmd.RunSQL strItemSQL
End Sub
I really appreciate any assistance with this! I have used this forum alot
and have learned a great deal!
Thans
Slez