how to keep open

[Bill H.]

I have a form on which I populate some unbound fields from a vba sql lookup
based on the contents of a combo box, which changes with each record

right now, I open the table via db.openrecordset and execute a sql string
(build in the module) with a
where clause, and then close out the db.

seems like a lot of opening/closing.

Is there a better and/or faster way to do this?

This is what it looks like now.


Dim db As Database, rs As DAO.Recordset, strSQL, strSQL1 As String
Set db = CurrentDb()

strSQL = "SELECT Qry_Union_FamilyMemberAll.RFS_ID_NO, [firstname] & ' ' &
[lastnameshort] " & _
"AS sName, Tbl_Family_Relation.FamilyRelation AS sRelation " & _
"FROM Qry_Union_FamilyMemberAll LEFT JOIN Tbl_Family_Relation " & _
"ON Qry_Union_FamilyMemberAll.Relationship = Tbl_Family_Relation.PriKey " &
_
"WHERE (((Qry_Union_FamilyMemberAll.RFS_ID_NO)='" & Me.RFS_ID_NO & "'))"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbOpenForwardOnly)

If Not rs.BOF And Not rs.EOF Then
Me.fName = rs!sName
Me.fRelation = rs!sRelation
endif

 

[Guest]

Since I am not sure of your table layout because you are using a query, I
don't know if this will work for you. If all three items you want are in the
same table, it will:
In the first line, notice the ":" - This will be used in the split function
to separate the name from the relation.

strRetVal = DLookup("[firstname] & [lastnameshort] & ':' &
[FamilyRelation]", _
& "MyFamilyTable", "[RFS_ID_NO] = '" & Me.RFS_ID_NO & "'")
If Not IsNull(strRetVal) Then
aryFam = Split(strRetVal,":") - creates an array splitting strRetVal
based on :
Me.fName = aryFam(0) - First element of the array will be name
Me.fRelation = aryFam(1) - second element of the array will be relation
Endif

I am not claiming this is any better, but worth consideration.

 

[Bill H.]

gimme a hint...

what would the Dim statement look like.

but, the values are not in the same table, thus the query. ;-)

Since I am not sure of your table layout because you are using a query, I
don't know if this will work for you. If all three items you want are in the
same table, it will:
In the first line, notice the ":" - This will be used in the split function
to separate the name from the relation.

strRetVal = DLookup("[firstname] & [lastnameshort] & ':' &
[FamilyRelation]", _
& "MyFamilyTable", "[RFS_ID_NO] = '" & Me.RFS_ID_NO & "'")
If Not IsNull(strRetVal) Then
aryFam = Split(strRetVal,":") - creates an array splitting strRetVal
based on :
Me.fName = aryFam(0) - First element of the array will be name
Me.fRelation = aryFam(1) - second element of the array will be relation
Endif

I am not claiming this is any better, but worth consideration.

I have a form on which I populate some unbound fields from a vba sql lookup
based on the contents of a combo box, which changes with each record

right now, I open the table via db.openrecordset and execute a sql string
(build in the module) with a
where clause, and then close out the db.

seems like a lot of opening/closing.

Is there a better and/or faster way to do this?

This is what it looks like now.


Dim db As Database, rs As DAO.Recordset, strSQL, strSQL1 As String
Set db = CurrentDb()

strSQL = "SELECT Qry_Union_FamilyMemberAll.RFS_ID_NO, [firstname] & ' ' &
[lastnameshort] " & _
"AS sName, Tbl_Family_Relation.FamilyRelation AS sRelation " & _
"FROM Qry_Union_FamilyMemberAll LEFT JOIN Tbl_Family_Relation " & _
"ON Qry_Union_FamilyMemberAll.Relationship = Tbl_Family_Relation.PriKey " &
_
"WHERE (((Qry_Union_FamilyMemberAll.RFS_ID_NO)='" & Me.RFS_ID_NO & "'))"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbOpenForwardOnly)

If Not rs.BOF And Not rs.EOF Then
Me.fName = rs!sName
Me.fRelation = rs!sRelation
endif

 

[Guest]

If they are not in the same table, then the way you are doing it is probably
best.

gimme a hint...

what would the Dim statement look like.

but, the values are not in the same table, thus the query. ;-)

Since I am not sure of your table layout because you are using a query, I
don't know if this will work for you. If all three items you want are in the
same table, it will:
In the first line, notice the ":" - This will be used in the split function
to separate the name from the relation.

strRetVal = DLookup("[firstname] & [lastnameshort] & ':' &
[FamilyRelation]", _
& "MyFamilyTable", "[RFS_ID_NO] = '" & Me.RFS_ID_NO & "'")
If Not IsNull(strRetVal) Then
aryFam = Split(strRetVal,":") - creates an array splitting strRetVal
based on :
Me.fName = aryFam(0) - First element of the array will be name
Me.fRelation = aryFam(1) - second element of the array will be relation
Endif

I am not claiming this is any better, but worth consideration.

I have a form on which I populate some unbound fields from a vba sql lookup
based on the contents of a combo box, which changes with each record

right now, I open the table via db.openrecordset and execute a sql string
(build in the module) with a
where clause, and then close out the db.

seems like a lot of opening/closing.

Is there a better and/or faster way to do this?

This is what it looks like now.


Dim db As Database, rs As DAO.Recordset, strSQL, strSQL1 As String
Set db = CurrentDb()

strSQL = "SELECT Qry_Union_FamilyMemberAll.RFS_ID_NO, [firstname] & ' ' &
[lastnameshort] " & _
"AS sName, Tbl_Family_Relation.FamilyRelation AS sRelation " & _
"FROM Qry_Union_FamilyMemberAll LEFT JOIN Tbl_Family_Relation " & _
"ON Qry_Union_FamilyMemberAll.Relationship = Tbl_Family_Relation.PriKey " &
_
"WHERE (((Qry_Union_FamilyMemberAll.RFS_ID_NO)='" & Me.RFS_ID_NO & "'))"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbOpenForwardOnly)

If Not rs.BOF And Not rs.EOF Then
Me.fName = rs!sName
Me.fRelation = rs!sRelation
endif