Whats Stable

[Guest]

A Dlookup is out of the question. It returns one field. (okay, I know that
is not really true, but to do so is impracticle)

The code you provided assumes the first record in the recordset is the one
you want.

Without knowing more about what you want, I would think the List Box might
be the better choice.

 

[DS]

Is Using a rowsource from a listbox,
a DLookup or this...

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tblCustomers WHERE
tblCustomers.CustomerID = " & Me.TxtCusID & "", dbOpenDynaset)
With rst
If Not .EOF Then
Me.TxtBusinessName = !CusBusinessName
Me.TxtDeeName = !CusFirstName & " " & !CusLastName
Me.TxtAddress = !CusStreetNumber & " " & !CusStreetName
Me.TxtApt = !CusApt
End If
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Which is more stable and les problematic?
Which is quicker?
Thanks
DS

 

[Guest]

Then I would use a DLookup

varCusRec = DLookup("[CusBusinessName] & '/' & [CusFirstName] & _
'/' & [CusLastName] & '/' & [CusStreetAddress] & '/' & _
[CusApt]", "tblCustomers", & "[CustomerID] = " & Me.txtCustID)

If Not IsNull(varCustRec) Then
varCusArray = Split(varCusRec, "/")
With Me
.TxtBusinessName = varCusArray(0)
.TxtDeeName = varCusArray(1) & " " & varCusArray(2)
.TxtAddress = varCusArray(3)
.TxtApt = varCusArray(4)
End With
End If

As I said earlier, it is possible to return multiple fields with a DLookup.
The trick is seperating them out again. For this we use the Split function
which creates an array. Then we load the elements of the array into the
unbound controls.

One thing to be aware of. If one of the fields is Null, the correspondiing
element in the array will not be Null, it will be "" or vbNullString.

 

[DS]

A Dlookup is out of the question. It returns one field. (okay, I know that
is not really true, but to do so is impracticle)

The code you provided assumes the first record in the recordset is the one
you want.

Without knowing more about what you want, I would think the List Box might
be the better choice.

The code I have, it only returns one record, (I only need one
record)that being based on CustomerID...I basically looking up the
values for unbound textboxes from a value in a listbox. I was just
trying to avoid having a listbox with a lot of columns. Does this
information Help?
Thanks
DS

 

[Guest]

Glad I could help
--
Dave Hargis, Microsoft Access MVP

Then I would use a DLookup

varCusRec = DLookup("[CusBusinessName] & '/' & [CusFirstName] & _
'/' & [CusLastName] & '/' & [CusStreetAddress] & '/' & _
[CusApt]", "tblCustomers", & "[CustomerID] = " & Me.txtCustID)

If Not IsNull(varCustRec) Then
varCusArray = Split(varCusRec, "/")
With Me
.TxtBusinessName = varCusArray(0)
.TxtDeeName = varCusArray(1) & " " & varCusArray(2)
.TxtAddress = varCusArray(3)
.TxtApt = varCusArray(4)
End With
End If

As I said earlier, it is possible to return multiple fields with a DLookup.
The trick is seperating them out again. For this we use the Split function
which creates an array. Then we load the elements of the array into the
unbound controls.

One thing to be aware of. If one of the fields is Null, the correspondiing
element in the array will not be Null, it will be "" or vbNullString.

Wow! I never knew that you could use DLookup to find multiple fields!
This is really exciting!
Thanks...!
DS

 

[DS]

Then I would use a DLookup

varCusRec = DLookup("[CusBusinessName] & '/' & [CusFirstName] & _
'/' & [CusLastName] & '/' & [CusStreetAddress] & '/' & _
[CusApt]", "tblCustomers", & "[CustomerID] = " & Me.txtCustID)

If Not IsNull(varCustRec) Then
varCusArray = Split(varCusRec, "/")
With Me
.TxtBusinessName = varCusArray(0)
.TxtDeeName = varCusArray(1) & " " & varCusArray(2)
.TxtAddress = varCusArray(3)
.TxtApt = varCusArray(4)
End With
End If

As I said earlier, it is possible to return multiple fields with a DLookup.
The trick is seperating them out again. For this we use the Split function
which creates an array. Then we load the elements of the array into the
unbound controls.

One thing to be aware of. If one of the fields is Null, the correspondiing
element in the array will not be Null, it will be "" or vbNullString.

Wow! I never knew that you could use DLookup to find multiple fields!
This is really exciting!
Thanks...!
DS

 

[David W. Fenton]

I never knew that you could use DLookup to find multiple fields!
This is really exciting!

It looks terrible to *me*. Anytime you are concatenating and then
splitting, it means you're doing something wrong.

For this kind of thing, I believe I'd use a function that returns a
recordset of the requested values, or perhaps an array. Or a sub
that uses ByRef. Generalizing either or those would be pretty easy,
actually.

 

[Guest]

I am doing nothing wrong. This is a perfectly acceptable technique. I am
sorry if you don't understand it.
Establishing a recordset to retrieve one row with four fields seems a bit of
an overkill to me.
I also find describing it as terrible very offensive to me personally. I
don't mind disagreement, but I think we can do it in a less personal and more
objective way.

 

[Marshall Barton]

I am doing nothing wrong. This is a perfectly acceptable technique. I am
sorry if you don't understand it.
Establishing a recordset to retrieve one row with four fields seems a bit of
an overkill to me.
I also find describing it as terrible very offensive to me personally. I
don't mind disagreement, but I think we can do it in a less personal and more
objective way.

Come on guys, lighten up a little.

At the risk of getting caught in the crossfire ;-)
I agree with Klatuu that "terrible" might be a little harsh.

OTOH, I agree with David that a recordset would be
preferable:

With OpenRecordset(" SELECT * FROM tblCustomers " _
& "WHERE CustomerID = " & Me.txtCustID
If .RecordCount > 0 Then
Me.TxtBusinessName = !CusBusinessName
Me.TxtDeeName = !CusFirstName & " " & !CusLastName
Me.TxtAddress = !CusStreetAddress
Me.TxtApt = !CusApt
End If
End With

 

[Guest]

I consider the recordset option reasonable and I did think about it before I
suggested the DLookup. My decision was based on the code necessary to
complete the operation and the execution time. Without testing, I can't be
sure, but my instinct is the DLookup, in this case, would be faster.

I am always open to discussion about techniques; however, even when I see
really bad ideas, I prefer to offer an alternative and justify it rather than
denigrating the the approach I think is inappropriate.

I, like most of us, have a certain pride of authorship and whether right or
wrong feel if you attack my work, you are attacking me. I will also grant
that I will, on occasion, get it wrong. I don't mind being corrected when it
is done in a diplomatic way.

If Mr. Fenton wants to disagree, then a reasoned discussion of the merits of
an idea is in order. Labeling another's work as terrible is, IMHO, over the
line.

I don't think I would characterize your post as getting into the crossfire.
I appreciate you input, Marsh.

 

[DS]

Klatuu wrote:





Come on guys, lighten up a little.

At the risk of getting caught in the crossfire ;-)
I agree with Klatuu that "terrible" might be a little harsh.

OTOH, I agree with David that a recordset would be
preferable:

With OpenRecordset(" SELECT * FROM tblCustomers " _
& "WHERE CustomerID = " & Me.txtCustID
If .RecordCount > 0 Then
Me.TxtBusinessName = !CusBusinessName
Me.TxtDeeName = !CusFirstName & " " & !CusLastName
Me.TxtAddress = !CusStreetAddress
Me.TxtApt = !CusApt
End If
End With

Marsh, This looks good also. Klatuu....it is not terrible. This
newsgroup is wonderful thing and everyone should be able to offer
solutions without be harshly criticized. Such words could make a person
not want to participate. Besides even if someone does think an idea is
bad, sometimes a good things have come out of bad ideas. You never know!!!!
Once again Thank You everyone for your help.
DS

 

[David W. Fenton]

I am doing nothing wrong.

Concatenating stuff just so you can split it looks like a mistake to
*me*.

This is a perfectly acceptable technique.

But not, in my opinion, optimal.

I am
sorry if you don't understand it.

I understand it perfectly well. In my day I wrote plenty of code
parsing Openargs parameters and that's exactly why I don't think
it's a good idea -- it's too inflexible and doesn't maintain data
types (as you point out yourself in regard to Nulls).

Establishing a recordset to retrieve one row with four fields
seems a bit of an overkill to me.
I also find describing it as terrible very offensive to me
personally.

I said it was *my* opinion. To quote:

I
don't mind disagreement, but I think we can do it in a less
personal and more objective way.

I think you definitely need to get a thicker skin.

 

[David W. Fenton]

Come on guys, lighten up a little.

At the risk of getting caught in the crossfire ;-)
I agree with Klatuu that "terrible" might be a little harsh.

OTOH, I agree with David that a recordset would be
preferable:

With OpenRecordset(" SELECT * FROM tblCustomers " _
& "WHERE
CustomerID = " & Me.txtCustID
If .RecordCount > 0 Then
Me.TxtBusinessName = !CusBusinessName
Me.TxtDeeName = !CusFirstName & " " & !CusLastName
Me.TxtAddress = !CusStreetAddress
Me.TxtApt = !CusApt
End If
End With

I wouldn't do it that way, because it's bound to the specific
circumstance. I would instead write code that is adaptable to many
situations. For instance, to do what you're doing above, my code
would accept a parameter array as the last argument that would
contain a list of control references. The code would then use the
..ControlSource property of the controls to look up the field value
in the recordsource and return an array of type Variant with the
values in it.

 

[David W. Fenton]

Labeling another's work as terrible is, IMHO, over the
line.

I didn't do that, David. I said:

Obviously, that means it's MY OPINION, not a statement of objective
fact.

 

[Guest]

And I think you need to learn good manners. Now as to my *terrible* way to
do it. Here are the statistics:

I used the following code to run these test:
*********************************************************
Public Function TimeDLookUp() As Long
Dim varStuff As Variant
Dim varItems As Variant

StartTimer
varStuff = DLookup("[ContractPropId] & '/' & [ContractType] & '/' &
[ContractStatus] & '/' & [PropertyEmployeeID]", "Contract", _
"[ContractPropId] = 'ING-WOO'")

If Not IsNull(varStuff) Then
varItems = Split(varStuff, "/")
Debug.Print varItems(0)
Debug.Print varItems(1)
Debug.Print varItems(2)
Debug.Print varItems(3)
End If
TimeDLookUp = StopTimer
End Function

Public Function TimeRS() As Long
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT ContractPropId, ContractType,
ContractStatus, " & _
"PropertyEmployeeID FROM Contract WHERE ContractPropID = 'ING-WOO';")

With rst
If .RecordCount > 0 Then
Debug.Print !ContractPropID
Debug.Print !ContractType
Debug.Print !ContractStatus
Debug.Print !PropertyEmployeeID
End If
.Close
End With
Set rst = Nothing
TimeRS = StopTimer
End Function

Public Function CompareTimes() As String
Dim lngX As Long
Dim lngTimeDL As Long
Dim lngTimeRS As Long

For lngX = 0 To 9
lngTimeDL = lngTimeDL + TimeDLookUp
lngTimeRS = lngTimeRS + TimeRS
Next lngX

CompareTimes = "DLookup " & CStr(lngTimeDL / 10) & " Recordset " &
CStr(lngTimeRS / 10)
End Function
********************************************
Using this api call to capture the time:
Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Private lngStartTime As Long

Public Sub StartTimer()
lngStartTime = timeGetTime()
End Sub

Public Function StopTimer() As Long
StopTimer = timeGetTime - lngStartTime
End Function
******************************************
The resutls are:
Dlookup Recordset
9.3 17.2
14 17.1
6.3 17.2
4.7 15.7
4.6 15.6
6.3 15.6
10.9 17.2
6.1 17.1
6.2 15.6
6.3 17.2

AVG AVG Advantage
7.47 16.55 0.451359517

Now, sir a think an apology is appropriate.
I don't mind disagreement. We all learn from it; however, your demeanor is
rude. If you need some coaching on this subject, read Douglas Steeles'
posts. He can disagree with you or point out your errors and you feel like a
friend has helped rather than you have been attacked by an arrogant advesrary.

 

[Guest]

Just for grins, I ran an additional set of test with the order of the calls
reversed. I was suprised at the difference.

Recordset First Dlookup First
Dlookup Recordset Dlookup Recordset
12.5 4201.6 9.3 17.2
10.9 3851.5 14 17.1
12.5 2220.3 6.3 17.2
12.5 450 4.7 15.7
11 1796.9 4.6 15.6
6.2 2070.3 6.3 15.6
6.3 1668.7 10.9 17.2
12.5 2479.7 6.1 17.1
1.6 1928.1 6.2 15.6
4.7 2475 6.3 17.2

Avg 9.07 2314.21 7.47 16.55
Min 1.6 450 4.6 15.6
Max 12.5 4201.6 14 17.2
Advantage 0.003919264 0.451359517

 

[Guest]

--
Dave Hargis, Microsoft Access MVP

Concatenating stuff just so you can split it looks like a mistake to
*me*.

Based on the test results, it is obviously not wrong

But not, in my opinion, optimal.

I believe the test results do not substantiate your opinion.

I understand it perfectly well. In my day I wrote plenty of code
parsing Openargs parameters and that's exactly why I don't think
it's a good idea -- it's too inflexible and doesn't maintain data
types (as you point out yourself in regard to Nulls).

We argree here. Parsing string has its faults and takes time. It is,
however, sometimes the better approach.

 

[Marshall Barton]

Marshall Barton wrote


I wouldn't do it that way, because it's bound to the specific
circumstance. I would instead write code that is adaptable to many
situations. For instance, to do what you're doing above, my code
would accept a parameter array as the last argument that would
contain a list of control references. The code would then use the
.ControlSource property of the controls to look up the field value
in the recordsource and return an array of type Variant with the
values in it.

I can see using a parameter array of control references, but
in the OP's situation, the controls are unbound so I don't
see how their control source can contribute any information.
There's also the issue of the result being the value of an
expression. While those concerns can be dealt with one way
or another, explaining all these generalities to someone
that needed to ask the original question is, IMO, not
productive.

Using a subform might be simpler than any of these code
techniques.

 

[DS]

David W. Fenton wrote:





I can see using a parameter array of control references, but
in the OP's situation, the controls are unbound so I don't
see how their control source can contribute any information.
There's also the issue of the result being the value of an
expression. While those concerns can be dealt with one way
or another, explaining all these generalities to someone
that needed to ask the original question is, IMO, not
productive.

Using a subform might be simpler than any of these code
techniques.

Thanks Marshall, there are several concerns, but at his point what I
have seems to work. So until something specific pops up, I'm fine.
I appreciate all of your help.
Thanks
DS

 

[David W. Fenton]

AVG AVG Advantage
7.47 16.55 0.451359517

Now, sir a think an apology is appropriate.

I made no performance claims at all, so your tests are completely
irrelevant.

Secondly, I owe you no apology. I've done nothing rude -- I've only
offered a different opinion.

I don't mind disagreement. We all learn from it; however, your
demeanor is rude. If you need some coaching on this subject,

I don't, as a matter of fact.

read Douglas Steeles'
posts. He can disagree with you or point out your errors and you
feel like a friend has helped rather than you have been attacked
by an arrogant advesrary.

I am arrogant, but I never attacked *you* -- I took issue with the
content of one of your posts. That isn't even close to being a
personal attack.