Hello,
I am using Access 2010 and I currently use a command button on a form to add new records to a table using data that the user has entered into the form using the code below:
Although this is pretty self-explanatory, here is a key for reference:
Me.lstFacilities = ListBox
Me.cboMeasure = ComboBox
Me.cboYesNo = ComboBox
Me.txtTarget = TextBox
This works great but I would like to be able to pull in data from another database based off of Me.lstFacilities.Column(1, i) which is the FACILITY_ID field and is located in the other database's table. I thought about adding in another string variable(strSQL1) and opening up a separate recordset and database:
However, I'm not sure where to start pulling in the data from the [fieldname] in the [tblOtherDatabase] when I start the loop below:
Is it even possible to do this? Please let me know if my question is ambiguous in any way. Thanks for any help!
I am using Access 2010 and I currently use a command button on a form to add new records to a table using data that the user has entered into the form using the code below:
Although this is pretty self-explanatory, here is a key for reference:
Me.lstFacilities = ListBox
Me.cboMeasure = ComboBox
Me.cboYesNo = ComboBox
Me.txtTarget = TextBox
Code:
Private Sub cmdAddMet_Click()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim strSQL As String
Dim i As Integer
Set DB = CurrentDb
strSQL = "SELECT * FROM tblFacRelationship"
Set RS = DB.OpenRecordset(strSQL)
For i = 0 To lstFacilities.ListCount - 1
If lstFacilities.Selected(i) = True Then
RS.AddNew
RS!RELATIONSHIP_ID = Me.lstFacilities.Column(0, i)
RS!MEASUREMENT_PERIOD = Me.cboMeasure
RS!GOALMET_NAME = Me.cboYesNo
RS!GOAL = Me.txtTarget
RS.Update
End If
Next i
RS.Close
Set RS = Nothing
Set DB = Nothing
End Sub
Code:
Set DB1 = OpenDatabase("\\server\otherdb.accdb")
strSQL1 = "SELECT [FieldName] FROM [tblOtherDatabase] IN '\\server\otherdb.accdb'"
Set RS1 = DB1.OpenRecordset(strSQL1)
However, I'm not sure where to start pulling in the data from the [fieldname] in the [tblOtherDatabase] when I start the loop below:
Code:
For i = 0 To lstFacilities.ListCount - 1
If lstFacilities.Selected(i) = True Then
RS.AddNew
RS!RELATIONSHIP_ID = Me.lstFacilities.Column(0, i)
RS!MEASUREMENT_PERIOD = Me.cboMeasure
RS!GOALMET_NAME = Me.cboYesNo
RS!GOAL = Me.txtTarget
RS.Update
End If
Next i
Is it even possible to do this? Please let me know if my question is ambiguous in any way. Thanks for any help!