Dlookup with 2 criteria

[Guest]

Hi,

I am trying to prefill a textbox (txtBudget) with the budget value found in
the testquery table. I have two criteria : cost code and category which are
both filled in using combo boxes (cboCC and cboCat).

I'm trying the cboCat afterupdate procedure to update the textbox but my
code isn't working. help! thanks a lot.

here is the code:

Private Sub cboCat_AfterUpdate()

Dim CC As String
Dim Cat As String
Dim result

CC = cboCC.Value
Cat = cboCat.Value

result = DLookup("[Total_Estimate]", "[testquery]", "[Cost_Code]= ' " & CC &
" ' and [Category] = " & Cat)

txtBudget.Value = result


End Sub

 

[Marshall Barton]

I am trying to prefill a textbox (txtBudget) with the budget value found in
the testquery table. I have two criteria : cost code and category which are
both filled in using combo boxes (cboCC and cboCat).

I'm trying the cboCat afterupdate procedure to update the textbox but my
code isn't working. help! thanks a lot.

here is the code:

Private Sub cboCat_AfterUpdate()

Dim CC As String
Dim Cat As String
Dim result

CC = cboCC.Value
Cat = cboCat.Value

result = DLookup("[Total_Estimate]", "[testquery]", "[Cost_Code]= ' " & CC &
" ' and [Category] = " & Cat)

txtBudget.Value = result
End Sub

Apparently the Cost_Code field is a text field and Category
is a numeric field. If that true, then the only thing I see
wrong is the extra spaces between the quotes and
apostrophes.

To avoid potential errors, you may want to add more code to
check that the two combo boxes actually have a value:

If Nz(Me.cboCC, "") = "" OR Nz(cboCat, "") = "" Then
Exit Sub
End If

 

[Guest]

Thanks. both of them are text fields. how should i change my code?

I am trying to prefill a textbox (txtBudget) with the budget value found in
the testquery table. I have two criteria : cost code and category which are
both filled in using combo boxes (cboCC and cboCat).

I'm trying the cboCat afterupdate procedure to update the textbox but my
code isn't working. help! thanks a lot.

here is the code:

Private Sub cboCat_AfterUpdate()

Dim CC As String
Dim Cat As String
Dim result

CC = cboCC.Value
Cat = cboCat.Value

result = DLookup("[Total_Estimate]", "[testquery]", "[Cost_Code]= ' " & CC &
" ' and [Category] = " & Cat)

txtBudget.Value = result
End Sub

Apparently the Cost_Code field is a text field and Category
is a numeric field. If that true, then the only thing I see
wrong is the extra spaces between the quotes and
apostrophes.

To avoid potential errors, you may want to add more code to
check that the two combo boxes actually have a value:

If Nz(Me.cboCC, "") = "" OR Nz(cboCat, "") = "" Then
Exit Sub
End If

 

[Guest]

If IsNull(CC) or CC = "" Then
MsgBox "No Value for CC"
Exit Sub
End If

If IsNull(Cat) or Cat = "" Then
MsgBox "No Value for Cat"
Exit Sub
End If

result = DLookup("[Total_Estimate]", "[testquery]", "[Cost_Code]= ' " & CC &
"' and [Category] = '" & Cat & "'")

If IsNull(result) Then
MsgBox "No Match Found for CC = " & CC & " Cat = " & cat
End If

Thanks. both of them are text fields. how should i change my code?

I am trying to prefill a textbox (txtBudget) with the budget value found in
the testquery table. I have two criteria : cost code and category which are
both filled in using combo boxes (cboCC and cboCat).

I'm trying the cboCat afterupdate procedure to update the textbox but my
code isn't working. help! thanks a lot.

here is the code:

Private Sub cboCat_AfterUpdate()

Dim CC As String
Dim Cat As String
Dim result

CC = cboCC.Value
Cat = cboCat.Value

result = DLookup("[Total_Estimate]", "[testquery]", "[Cost_Code]= ' " & CC &
" ' and [Category] = " & Cat)

txtBudget.Value = result
End Sub

Apparently the Cost_Code field is a text field and Category
is a numeric field. If that true, then the only thing I see
wrong is the extra spaces between the quotes and
apostrophes.

To avoid potential errors, you may want to add more code to
check that the two combo boxes actually have a value:

If Nz(Me.cboCC, "") = "" OR Nz(cboCat, "") = "" Then
Exit Sub
End If

 

[Guest]

Thanks so much.

Now I have another issue on this:

It's working very slow as it uses a query to find the values. I created a
make-table query so that it would work faster. How can I automate the make
table query to run when the form loads?

I added a code to the form load function but it's giving me an error. all i
did was DoCmd.OpenQuery qryCat3, acViewNormal, acReadOnly

thanks again for your help!

Michelle K

If IsNull(CC) or CC = "" Then
MsgBox "No Value for CC"
Exit Sub
End If

If IsNull(Cat) or Cat = "" Then
MsgBox "No Value for Cat"
Exit Sub
End If

result = DLookup("[Total_Estimate]", "[testquery]", "[Cost_Code]= ' " & CC &
"' and [Category] = '" & Cat & "'")

If IsNull(result) Then
MsgBox "No Match Found for CC = " & CC & " Cat = " & cat
End If

Thanks. both of them are text fields. how should i change my code?

Michelle K wrote:
I am trying to prefill a textbox (txtBudget) with the budget value found in
the testquery table. I have two criteria : cost code and category which are
both filled in using combo boxes (cboCC and cboCat).

I'm trying the cboCat afterupdate procedure to update the textbox but my
code isn't working. help! thanks a lot.

here is the code:

Private Sub cboCat_AfterUpdate()

Dim CC As String
Dim Cat As String
Dim result

CC = cboCC.Value
Cat = cboCat.Value

result = DLookup("[Total_Estimate]", "[testquery]", "[Cost_Code]= ' " & CC &
" ' and [Category] = " & Cat)

txtBudget.Value = result
End Sub


Apparently the Cost_Code field is a text field and Category
is a numeric field. If that true, then the only thing I see
wrong is the extra spaces between the quotes and
apostrophes.

To avoid potential errors, you may want to add more code to
check that the two combo boxes actually have a value:

If Nz(Me.cboCC, "") = "" OR Nz(cboCat, "") = "" Then
Exit Sub
End If

 

[Guest]

The Open event should be the place to do it, provided the make tabke query
has all the criteria it needs without any data on the form.
I can't imagine making a table every time you open the form would be faster.
Is it slow on the first record and maybe faster after that?

Thanks so much.

Now I have another issue on this:

It's working very slow as it uses a query to find the values. I created a
make-table query so that it would work faster. How can I automate the make
table query to run when the form loads?

I added a code to the form load function but it's giving me an error. all i
did was DoCmd.OpenQuery qryCat3, acViewNormal, acReadOnly

thanks again for your help!

Michelle K

If IsNull(CC) or CC = "" Then
MsgBox "No Value for CC"
Exit Sub
End If

If IsNull(Cat) or Cat = "" Then
MsgBox "No Value for Cat"
Exit Sub
End If

result = DLookup("[Total_Estimate]", "[testquery]", "[Cost_Code]= ' " & CC &
"' and [Category] = '" & Cat & "'")

If IsNull(result) Then
MsgBox "No Match Found for CC = " & CC & " Cat = " & cat
End If

Thanks. both of them are text fields. how should i change my code?



:

Michelle K wrote:
I am trying to prefill a textbox (txtBudget) with the budget value found in
the testquery table. I have two criteria : cost code and category which are
both filled in using combo boxes (cboCC and cboCat).

I'm trying the cboCat afterupdate procedure to update the textbox but my
code isn't working. help! thanks a lot.

here is the code:

Private Sub cboCat_AfterUpdate()

Dim CC As String
Dim Cat As String
Dim result

CC = cboCC.Value
Cat = cboCat.Value

result = DLookup("[Total_Estimate]", "[testquery]", "[Cost_Code]= ' " & CC &
" ' and [Category] = " & Cat)

txtBudget.Value = result
End Sub


Apparently the Cost_Code field is a text field and Category
is a numeric field. If that true, then the only thing I see
wrong is the extra spaces between the quotes and
apostrophes.

To avoid potential errors, you may want to add more code to
check that the two combo boxes actually have a value:

If Nz(Me.cboCC, "") = "" OR Nz(cboCat, "") = "" Then
Exit Sub
End If

 

[Guest]

No. It seems like it's querying each time. the table values change each time
they open it anyway so that should be ok i think.

The Open event should be the place to do it, provided the make tabke query
has all the criteria it needs without any data on the form.
I can't imagine making a table every time you open the form would be faster.
Is it slow on the first record and maybe faster after that?

Thanks so much.

Now I have another issue on this:

It's working very slow as it uses a query to find the values. I created a
make-table query so that it would work faster. How can I automate the make
table query to run when the form loads?

I added a code to the form load function but it's giving me an error. all i
did was DoCmd.OpenQuery qryCat3, acViewNormal, acReadOnly

thanks again for your help!

Michelle K

If IsNull(CC) or CC = "" Then
MsgBox "No Value for CC"
Exit Sub
End If

If IsNull(Cat) or Cat = "" Then
MsgBox "No Value for Cat"
Exit Sub
End If

result = DLookup("[Total_Estimate]", "[testquery]", "[Cost_Code]= ' " & CC &
"' and [Category] = '" & Cat & "'")

If IsNull(result) Then
MsgBox "No Match Found for CC = " & CC & " Cat = " & cat
End If

:

Thanks. both of them are text fields. how should i change my code?



:

Michelle K wrote:
I am trying to prefill a textbox (txtBudget) with the budget value found in
the testquery table. I have two criteria : cost code and category which are
both filled in using combo boxes (cboCC and cboCat).

I'm trying the cboCat afterupdate procedure to update the textbox but my
code isn't working. help! thanks a lot.

here is the code:

Private Sub cboCat_AfterUpdate()

Dim CC As String
Dim Cat As String
Dim result

CC = cboCC.Value
Cat = cboCat.Value

result = DLookup("[Total_Estimate]", "[testquery]", "[Cost_Code]= ' " & CC &
" ' and [Category] = " & Cat)

txtBudget.Value = result
End Sub


Apparently the Cost_Code field is a text field and Category
is a numeric field. If that true, then the only thing I see
wrong is the extra spaces between the quotes and
apostrophes.

To avoid potential errors, you may want to add more code to
check that the two combo boxes actually have a value:

If Nz(Me.cboCC, "") = "" OR Nz(cboCat, "") = "" Then
Exit Sub
End If