DLookup Alternative

It took a while to get it all in because of the amount of information needed,
but it works.

I owe you a steak dinner, or lobster if no red meat, or veggie burger if
vegan. Thank you SO much!! Thanks for your patience as well. I think I'm
getting burned out with Access and may need a little bit of a break soon.

"Klatuu" wrote:

Column numbers start at 0, not 1. 1 would be the second column, so it looks
like you are going 1 beyond the number of columns, but if you just want to
find an existing record in a form using an unbound combo, then this is how it
is normally done:

Private Sub SITE_NUMBER_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[SITE_NUMBER] = " & Me.SITE_NUMBER
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

But, since you want to be able to seach using different fields, you can do a
couple of things. If you only want to return one record, you can create a
different combo for each field you want to search on or you can get fancy and
use only one combo. You can do that with an Option Group and a combo. Use
the Option Group to select a field to search on. In the After Update event
of the Option group, set the combo's row source. In the After Update event,
check the value of the option group to determine which field to search on. I
have an example of that, but I can't find it at the moment.

Another technique is to have multiple combos so you can filter your form
based on the value of any or all of the combos.

Here is how you can do that:

Private Function SetInitFilters() As String
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetInitFilters_Error

With Me

'Build The Filter String
If .cboPriority "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigQtr "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseQtr], '@@@@-@') =
""" & _
.cboOrigQtr & """"
End If

If .cboCurrQtr "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrReleaseQtr], '@@@@-@') =
""" & _
.cboCurrQtr & """"
End If

If .cboInitStatus 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.cboCenter

If Len(strFilter) 0 Then
.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery
Else
.subInitiative.Form.FilterOn = False
End If

End With 'Me

SetInitFilters = strFilter
-------------------------------------------

Private Function AddAnd(strFilterString) As String

If Len(strFilterString) 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If
End Function

The last trick for that is how you set a combo's row source. You use which
ever meets the need.
This is for when you are searching for a specific value in a field:
SELECT "(All)" As Dummy FROM dbo_Initiative UNION SELECT DISTINCT
InitPriority FROM dbo_Initiative WHERE InitPriority IS NOT NULL;

This is when you are searching on a field that is a foreign key field:

SELECT 0 As ID, "(All)" As Dummy FROM dbo_InitStatus UNION SELECT
dbo_InitStatus.InitStatID, dbo_InitStatus.InitStatDescr FROM dbo_InitStatus;
--
Dave Hargis, Microsoft Access MVP


"Lena" wrote:

Correct. I have tried another method, but without success. I believe I am
missing part of my code.

Attempt #1:

Private Sub SITE_NUMBER_AfterUpdate()
Me.Facility_ID = Me.SITE_NUMBER.Column(1)
Me.FACILITY_NAME = Me.SITE_NUMBER.Column(2)
Me.Phone_Number = Me.SITE_NUMBER.Column(3)
Me.ADDRESS = Me.SITE_NUMBER.Column(4)
Me.CITY = Me.SITE_NUMBER.Column(5)
Me.ZIP_CODE = Me.SITE_NUMBER.Column(6)
Me.COUNTY = Me.SITE_NUMBER.Column(7)
'I then get a crash and repair after column(7).

Attempt# 2:
'Tried to create unbound combo boxes and attach the following code.

Private Sub cboSITE_NUMBER_AfterUpdate()
Me.cboLOCATION.RowSourceType = "Facilities"
Me.cboLOCATION.RowSource = "SELECT DISTINCT [" & _
Me.cboSITE_NUMBER & "] FROM [" & _
Me.cboSITE_NUMBER.RowSource & _
"] ORDER BY [" & Me.cboSITE_NUMBER & "]"
End Sub

'Again, no success. I then attempted to run the above code against the
original combo boxes, which are actually field names in a table (where data
from form insertion is stored).

"Klatuu" wrote:

Not defeated by a long shot. I think maybe I understand the problem, but I
need to to clarify or correct me.
It sounds like you are trying to use a combo box to find a specific record
and make that the current record for the form. But, you also want to be able
to find that record by one of several fields.

Is that it?

If so, it is a piece of cake (if you are a good bakerg)
Let me know, I am sure we can do it.
--
Dave Hargis, Microsoft Access MVP


"Lena" wrote:

Yes, this is a bound form. It's bound to a query in the background, not the
table. I have tried everything to get this combo box to work, nothing seems
to work. I'm running out of tricks. I've tried the Column(1), I've tried
the Dlookup, I've tried the subform associated with the SITE NUMBERS, etc.,
there are too many variables associated with the site number, only the site
name cannot be repeated. I have an associated number for these sites, but
even this will not work. I'm just about ready to chuck it and try again with
another program to see if I can get SOMETHING to work if ANYTHING.

Sheesh!!! I hate defeat.

"Klatuu" wrote:

Is this a bound form?
If not, why not?
If you are using an unbound form, use a query to return the records and open
the query as a recordset. Then you can use MoveNext, MovePrevious,
MoveFirst, or Move last to navigate through the records. Each time you move
to a new record, you can populate the controls from the fields in the query.

Now, if you just use a bound form, Access will do all that for you.

--
Dave Hargis, Microsoft Access MVP


"Lena" wrote:

I am looking for an alternative to Dlookup to autofill a form. Because this
process is very slow and we are working across a WAN, I do not want to bog
the system down more than I have to. Also, since Dlookup only provides the
first occurrence of the search, if I have other records associated with my
Dlookup request, I will only get the first instead of let's say maybe the
third or fourth instance of the data needed for the new record. This is what
I have used for my Dlookup:

Private Sub SITE_NUMBER_AfterUpdate()
If (DLookup("[SITE_NUMBER]", "Facilities", "[SITE_NUMBER] = Forms![PC
Form]![SITE_NUMBER]") "") Then
Me![Facility_ID] = DLookup("[FACILITY_ID]", "Facilities", "[SITE_NUMBER] =
Forms![PC Form]![SITE_NUMBER]")
Me![FACILITY_NAME] = DLookup("[FACILITY_NAME]", "Facilities", "[SITE_NUMBER]
= Forms![PC Form]![SITE_NUMBER]")
Me![Region] = DLookup("[REGION]", "Facilities", "[SITE_NUMBER] = Forms![PC
Form]![SITE_NUMBER]")
Me![ADDRESS] = DLookup("[ADDRESS]", "Facilities", "[SITE_NUMBER] = Forms![PC
Form]![SITE_NUMBER]")
Me![CITY] = DLookup("[CITY]", "Facilities", "[SITE_NUMBER] = Forms![PC
Form]![SITE_NUMBER]")
Me![COUNTY] = DLookup("[COUNTY]", "Facilities", "[SITE_NUMBER] = Forms![PC
Form]![SITE_NUMBER]")
Me![ZIP CODE] = DLookup("[ZIP_CODE]", "Facilities", "[SITE_NUMBER] =
Forms![PC Form]![SITE_NUMBER]")
Me![Phone_Number] = DLookup("[PHONE]", "Facilities", "[SITE_NUMBER] =
Forms![PC Form]![SITE_NUMBER]")

This is much TOOOOO slow and with it going across the WAN, I am only asking
for trouble. Plus I only get the first record related to the SITE_NUMBER,
not all records associated with the SITE_NUMBER.

Glad you got it working.
I haven't had a steak since.......yesterday
--
Dave Hargis, Microsoft Access MVP


"Lena" wrote:

It took a while to get it all in because of the amount of information needed,
but it works.

I owe you a steak dinner, or lobster if no red meat, or veggie burger if
vegan. Thank you SO much!! Thanks for your patience as well. I think I'm
getting burned out with Access and may need a little bit of a break soon.

"Klatuu" wrote:

Column numbers start at 0, not 1. 1 would be the second column, so it looks
like you are going 1 beyond the number of columns, but if you just want to
find an existing record in a form using an unbound combo, then this is how it
is normally done:

Private Sub SITE_NUMBER_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[SITE_NUMBER] = " & Me.SITE_NUMBER
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

But, since you want to be able to seach using different fields, you can do a
couple of things. If you only want to return one record, you can create a
different combo for each field you want to search on or you can get fancy and
use only one combo. You can do that with an Option Group and a combo. Use
the Option Group to select a field to search on. In the After Update event
of the Option group, set the combo's row source. In the After Update event,
check the value of the option group to determine which field to search on. I
have an example of that, but I can't find it at the moment.

Another technique is to have multiple combos so you can filter your form
based on the value of any or all of the combos.

Here is how you can do that:

Private Function SetInitFilters() As String
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetInitFilters_Error

With Me

'Build The Filter String
If .cboPriority "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigQtr "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseQtr], '@@@@-@') =
""" & _
.cboOrigQtr & """"
End If

If .cboCurrQtr "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrReleaseQtr], '@@@@-@') =
""" & _
.cboCurrQtr & """"
End If

If .cboInitStatus 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.cboCenter

If Len(strFilter) 0 Then
.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery
Else
.subInitiative.Form.FilterOn = False
End If

End With 'Me

SetInitFilters = strFilter
-------------------------------------------

Private Function AddAnd(strFilterString) As String

If Len(strFilterString) 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If
End Function

The last trick for that is how you set a combo's row source. You use which
ever meets the need.
This is for when you are searching for a specific value in a field:
SELECT "(All)" As Dummy FROM dbo_Initiative UNION SELECT DISTINCT
InitPriority FROM dbo_Initiative WHERE InitPriority IS NOT NULL;

This is when you are searching on a field that is a foreign key field:

SELECT 0 As ID, "(All)" As Dummy FROM dbo_InitStatus UNION SELECT
dbo_InitStatus.InitStatID, dbo_InitStatus.InitStatDescr FROM dbo_InitStatus;
--
Dave Hargis, Microsoft Access MVP


"Lena" wrote:

Correct. I have tried another method, but without success. I believe I am
missing part of my code.

Attempt #1:

Private Sub SITE_NUMBER_AfterUpdate()
Me.Facility_ID = Me.SITE_NUMBER.Column(1)
Me.FACILITY_NAME = Me.SITE_NUMBER.Column(2)
Me.Phone_Number = Me.SITE_NUMBER.Column(3)
Me.ADDRESS = Me.SITE_NUMBER.Column(4)
Me.CITY = Me.SITE_NUMBER.Column(5)
Me.ZIP_CODE = Me.SITE_NUMBER.Column(6)
Me.COUNTY = Me.SITE_NUMBER.Column(7)
'I then get a crash and repair after column(7).

Attempt# 2:
'Tried to create unbound combo boxes and attach the following code.

Private Sub cboSITE_NUMBER_AfterUpdate()
Me.cboLOCATION.RowSourceType = "Facilities"
Me.cboLOCATION.RowSource = "SELECT DISTINCT [" & _
Me.cboSITE_NUMBER & "] FROM [" & _
Me.cboSITE_NUMBER.RowSource & _
"] ORDER BY [" & Me.cboSITE_NUMBER & "]"
End Sub

'Again, no success. I then attempted to run the above code against the
original combo boxes, which are actually field names in a table (where data
from form insertion is stored).

"Klatuu" wrote:

Not defeated by a long shot. I think maybe I understand the problem, but I
need to to clarify or correct me.
It sounds like you are trying to use a combo box to find a specific record
and make that the current record for the form. But, you also want to be able
to find that record by one of several fields.

Is that it?

If so, it is a piece of cake (if you are a good bakerg)
Let me know, I am sure we can do it.
--
Dave Hargis, Microsoft Access MVP


"Lena" wrote:

Yes, this is a bound form. It's bound to a query in the background, not the
table. I have tried everything to get this combo box to work, nothing seems
to work. I'm running out of tricks. I've tried the Column(1), I've tried
the Dlookup, I've tried the subform associated with the SITE NUMBERS, etc.,
there are too many variables associated with the site number, only the site
name cannot be repeated. I have an associated number for these sites, but
even this will not work. I'm just about ready to chuck it and try again with
another program to see if I can get SOMETHING to work if ANYTHING.

Sheesh!!! I hate defeat.

"Klatuu" wrote:

Is this a bound form?
If not, why not?
If you are using an unbound form, use a query to return the records and open
the query as a recordset. Then you can use MoveNext, MovePrevious,
MoveFirst, or Move last to navigate through the records. Each time you move
to a new record, you can populate the controls from the fields in the query.

Now, if you just use a bound form, Access will do all that for you.

--
Dave Hargis, Microsoft Access MVP


"Lena" wrote:

I am looking for an alternative to Dlookup to autofill a form. Because this
process is very slow and we are working across a WAN, I do not want to bog
the system down more than I have to. Also, since Dlookup only provides the
first occurrence of the search, if I have other records associated with my
Dlookup request, I will only get the first instead of let's say maybe the
third or fourth instance of the data needed for the new record. This is what
I have used for my Dlookup:

Private Sub SITE_NUMBER_AfterUpdate()
If (DLookup("[SITE_NUMBER]", "Facilities", "[SITE_NUMBER] = Forms![PC
Form]![SITE_NUMBER]") "") Then
Me![Facility_ID] = DLookup("[FACILITY_ID]", "Facilities", "[SITE_NUMBER] =
Forms![PC Form]![SITE_NUMBER]")
Me![FACILITY_NAME] = DLookup("[FACILITY_NAME]", "Facilities", "[SITE_NUMBER]
= Forms![PC Form]![SITE_NUMBER]")
Me![Region] = DLookup("[REGION]", "Facilities", "[SITE_NUMBER] = Forms![PC
Form]![SITE_NUMBER]")
Me![ADDRESS] = DLookup("[ADDRESS]", "Facilities", "[SITE_NUMBER] = Forms![PC
Form]![SITE_NUMBER]")
Me![CITY] = DLookup("[CITY]", "Facilities", "[SITE_NUMBER] = Forms![PC
Form]![SITE_NUMBER]")
Me![COUNTY] = DLookup("[COUNTY]", "Facilities", "[SITE_NUMBER] = Forms![PC
Form]![SITE_NUMBER]")
Me![ZIP CODE] = DLookup("[ZIP_CODE]", "Facilities", "[SITE_NUMBER] =
Forms![PC Form]![SITE_NUMBER]")
Me![Phone_Number] = DLookup("[PHONE]", "Facilities", "[SITE_NUMBER] =
Forms![PC Form]![SITE_NUMBER]")

This is much TOOOOO slow and with it going across the WAN, I am only asking
for trouble. Plus I only get the first record related to the SITE_NUMBER,
not all records associated with the SITE_NUMBER.