Open Edit Form with selected information

[Ossos]

Hi



I have a list of Rooms (listed in SubForm named "3_LongFacRoomSubForm") I want to edit any record in this list via a form named "3_Fac_Rooms_Name_Edit" after Click Command Button named "CmdFacRoomEdit

Private Sub CmdFacRoomEdit_Click()

MsgBox [3_LongFacRoomSubForm]!Med_Fac_Tag
MsgBox [3_LongFacRoomSubForm]!TxtRoomNo

Dim SQL As String

SQL = "SELECT [3_Fac_Rooms_List].RoomTypeID, [3_Fac_Rooms_List].MedFacRoomName, [3_Fac_Rooms_List].MedFacRoomNo," _
& "[3_Fac_Rooms_List].RoomCode, [3_Fac_Rooms_List].KeyRoomShortList_ID, [3_Fac_Rooms_List].Med_Fac_Room_Note, " _
& "[3_Fac_Rooms_List].Med_Auth_ID, [3_Fac_Rooms_List].Project_ID, [3_Fac_Rooms_Edit].Med_Fac_Tag " _
& "FROM 3_Fac_Rooms_List " _
& "WHERE [3_Fac_Rooms_Name_Edit].MedFacRoomNo = '" & [3_LongFacRoomSubForm]!TxtRoomNo & "' " _
& "AND [3_Fac_Rooms_Name_Edit].Med_Fac_Tag = '" & [3_LongFacRoomSubForm]!Med_Fac_Tag & "' "

DoCmd.OpenForm "3_Fac_Rooms_Name_Edit", , , , acFormEdit

Forms![3_Fac_Rooms_Name_Edit].Form.RecordSource = SQL
Forms![3_Fac_Rooms_Name_Edit].Form.Requery

End Sub


When I run it a popup window ask to enter the parameter value of "3_Fac_Rooms_Edit.Med_Fac_Tag" then the same for "3_Fac_Rooms_Name_Edit.MedFacRoomNo" for several times, then the Edit window open with blank fields

[ItsMe]

Apparently, there is not a column named Med_Fac_Tag in table/query 3_Fac_Rooms_Edit and not a column named MedFacRoomNo in table/query 3_Fac_Rooms_Name_Edit

[Ossos]

Thank you

But The Columns already exist see attached.

[Ossos]

and the below is the Table structure

[ItsMe]

When I run it a popup window ask to enter the parameter value of "3_Fac_Rooms_Edit.Med_Fac_Tag" then the same for "3_Fac_Rooms_Name_Edit.MedFacRoomNo" for several times, then the Edit window open with blank fields

The message you are getting from Access is stating that you do not have a Table or Query named 3_Fac_Rooms_Edit or 3_Fac_Rooms_Name_Edit with the respective column names.

You have provided the design view of one table. That table name does not match either of the names provided by the Access message.

Take a look at your SQL. Look at the WHERE clause.

& "WHERE [3_Fac_Rooms_Name_Edit].MedFacRoomNo = '" & [3_LongFacRoomSubForm]!TxtRoomNo & "' " _
& "AND [3_Fac_Rooms_Name_Edit].Med_Fac_Tag = '" & [3_LongFacRoomSubForm]!Med_Fac_Tag & "' "

You are asking your query to use columns MedFacRoomNo and Med_Fac_Tag for table 3_Fac_Rooms_Name_Edit

But yeah, I think there is even more going on here. There may be an issue with the Bound Controls and their data source. So you might want to review your combo bound column, etc.

I say fix your SQL for the subform's recordsource first. Make sure you have the correct control name for the controls in the subform you are referencing.
????
= '" & [3_LongFacRoomSubForm]!Med_Fac_Tag & "'

[ItsMe]

I made some edits to #5 by adding additional comments ...

[Ossos]

Thank you so much

I made the following changes

I get rid of Select statement, and used the DoCmd.OpenForm, then in Where section i phrase it like this

DoCmd.OpenForm "3_Fac_Rooms_Name_Edit", acNormal, "", "[MedFacRoomNo]=" & " '" & Forms![3_LongFacRoomMainForm]![3_LongFacRoomSubform]![MedFacRoomNo] & "' & "AND [Med_Fac_Tag]=" & " '" & Forms![3_LongFacRoomMainForm]![CmbFacTagName] & "' "

Thank you again