Jenn

[Pinky69]

I have a form with several subforms for entering information while surveying rooms in a building. I am trying to create a button to copy records from one of the subforms if the data is the same (for example if there are 3 types of flooring in multiple rooms) and append it into the same subform with a different space ID. I can not get it to recognize the Space ID on the current record. This is what I have in the command button code:
Private Sub AppendFloorCmd_Click()

Dim FloorTypes As String, SpaceUpdate As String
Dim CurrentSpace As TextBox
Set CurrentSpace = Me.SpaceID

FloorTypes = "INSERT INTO FlooringSurveyTable (FlooringHomoID)" & _
"SELECT FlooringSurveyTable.FlooringHomoID FROM FlooringSurveyTable " & _
"WHERE (((FlooringSurveyTable.SpaceID)=[Enter Space ID to copy]))"

DoCmd.SetWarnings False

DoCmd.RunSQL FloorTypes

SpaceUpdate = "UPDATE FlooringSurveyTable SET FlooringSurveyTable.SpaceID = CurrentSpace WHERE (((FlooringSurveyTable.SpaceID) Is Null))"

DoCmd.RunSQL SpaceUpdate

DoCmd.SetWarnings True

Me.Requery

End Sub
When I click the button, it appends the right records but makes me type in the current space ID - I've tried a bunch of ways of naming that control, but it will not work. Any help would be greatly appreciated.

 

[pbaldy]

Well, you aren't referring to a control, this is designed to prompt:

[Enter Space ID to copy]

To refer to a control you'd concatenate a reference to it into the string:

"WHERE FlooringSurveyTable.SpaceID = " & Me.SpaceID

 

[Pinky69]

Re: Copying records with new ID

That's right - I want it to prompt to enter the space to copy from, append the flooring from that space (all HomoIDs for flooring) but capture the new space ID to use for the appended records. Does that make sense? The code I posted does append the flooring from the original space but it doesn't copy the Space ID from the new space - it gives me a box to enter the new Spcace ID like it doesn't recognize the name of the textbox. I have a Spaces Form and a subform for Flooring. I am not experienced with VBA so I think I am just not referencing that textbox correctly...

I just tried adding the Space ID to the Append Query but it still asks me to supply the CurrentSpace field. Are you saying I should have the SpaceID outside the quotes in my SQL string? I tried it but it gave me a syntax error. I would really appreciate any help on this - I've tried everything I can come up with...

 

[pbaldy]

The variable CurrentSpace needs to be concatenated into the string. It is only known within VBA, and you're passing the SQL to the database engine to execute. It doesn't know what it is, so prompts for it. Presuming its data type is numeric:

SpaceUpdate = "UPDATE FlooringSurveyTable SET FlooringSurveyTable.SpaceID = " & CurrentSpace & " WHERE (((FlooringSurveyTable.SpaceID) Is Null))"

 

[Pinky69]

It is a text field - our field staff often use letters in the Space IDs...so that gave me a syntax error...could you give the syntax for text? Thank you so much!

 

[pbaldy]

Try

SpaceUpdate = "UPDATE FlooringSurveyTable SET FlooringSurveyTable.SpaceID = '" & CurrentSpace & "' WHERE (((FlooringSurveyTable.SpaceID) Is Null))"

 

[Pinky69]

That did it!!!! Thank you so much - I've been trying to get that to work for a week!!

 

[pbaldy]

Happy to help, and welcome to the site by the way!