DLookup

D

David M C

Private Sub SORCode_AfterUpdate()


If Me![SORCode] = Null Then
'Do Nothing
Else
Me![Description] = DLookup("[Description]", "ScheduleOfRates",
"[SORCode] = " & Me![SORCode])
Me![Units] = DLookup("[Units]", "ScheduleOfRates", "[SORCode] = " &
Me![SORCode])
Me![Price] = DLookup("[Price]", "ScheduleOfRates", "[SORCode] = " &
Me![SORCode])
End If

Me.Requery

End Sub

I have a table, WorksDetail and a table ScheduleOfRates. My form puts
records into the WorksDetail table. For about 25% of the records, we use what
is known as the Schedule of Rates, which is an industry standard system of
pricing. When one of these codes is entered, I'm trying to "auto-populate"
the Description, Units, and Price fields of my form (or write these records
to the appropriate table).

I've tried using the above DLookup code but I get a data type mismatch error.

Where am I going wrong?

ScheduleOfRates:

SORCode : Text
Description : Text
Units : Text
Price : Currency

WorksDetail:

SORCode : Text
Description: Text
Quantity : Number
Units : Text
Price : Currency
 
O

Ofer Cohen

First use IsNull instead of = Null

Try

If IsNull(Me![SORCode]) Then
Me![Description] = DLookup("[Description]", "ScheduleOfRates",
"[SORCode] = '" & Me![SORCode] & "'")
Me![Units] = DLookup("[Units]", "ScheduleOfRates", "[SORCode] = '" &
Me![SORCode] & "'")
Me![Price] = DLookup("[Price]", "ScheduleOfRates", "[SORCode] = '" &
Me![SORCode] & "'")

End If

Because the SORCode is a text type field you need to add a single (') before
and after, without it, it will treat it as number
 
O

Ofer Cohen

Try ths SQL

UPDATE TableName INNER JOIN ScheduleOfRates ON TableName.SORCodes =
ScheduleOfRates.SORCodes SET TableName.[descriptions] =
ScheduleOfRates.[descriptions]
WHERE (TableName.descriptions) Is Null

You can do the same with the rest of the fields

Though, why do you save this values, it's better to display the values using
a select query instead of updating the exisiting fields, linking the two
tables.
What happen when the description in table ScheduleOfRates changes, do you
need to update the other table?

--
Good Luck
BS"D


David M C said:
Great, thanks, got it working.

One more question. With my tanle design change, in WorksDetail I now have a
lot of SORCodes without the descriptions, units etc. How can I cycle through
every record doing a DLookup and updating the appropriate fields (the fields
I'm updating automatically in the form? I'm sure it's a simple VBA procedure
but I haven't quite got the hang of recordsets etc.

Thanks

Dave

Ofer Cohen said:
First use IsNull instead of = Null

Try

If IsNull(Me![SORCode]) Then
Me![Description] = DLookup("[Description]", "ScheduleOfRates",
"[SORCode] = '" & Me![SORCode] & "'")
Me![Units] = DLookup("[Units]", "ScheduleOfRates", "[SORCode] = '" &
Me![SORCode] & "'")
Me![Price] = DLookup("[Price]", "ScheduleOfRates", "[SORCode] = '" &
Me![SORCode] & "'")

End If

Because the SORCode is a text type field you need to add a single (') before
and after, without it, it will treat it as number
--
Good Luck
BS"D


David M C said:
Private Sub SORCode_AfterUpdate()


If Me![SORCode] = Null Then
'Do Nothing
Else
Me![Description] = DLookup("[Description]", "ScheduleOfRates",
"[SORCode] = " & Me![SORCode])
Me![Units] = DLookup("[Units]", "ScheduleOfRates", "[SORCode] = " &
Me![SORCode])
Me![Price] = DLookup("[Price]", "ScheduleOfRates", "[SORCode] = " &
Me![SORCode])
End If

Me.Requery

End Sub

I have a table, WorksDetail and a table ScheduleOfRates. My form puts
records into the WorksDetail table. For about 25% of the records, we use what
is known as the Schedule of Rates, which is an industry standard system of
pricing. When one of these codes is entered, I'm trying to "auto-populate"
the Description, Units, and Price fields of my form (or write these records
to the appropriate table).

I've tried using the above DLookup code but I get a data type mismatch error.

Where am I going wrong?

ScheduleOfRates:

SORCode : Text
Description : Text
Units : Text
Price : Currency

WorksDetail:

SORCode : Text
Description: Text
Quantity : Number
Units : Text
Price : Currency
Click to expand...
Click to expand...
Click to expand...
 
D

David M C

Great, thanks, got it working.

One more question. With my tanle design change, in WorksDetail I now have a
lot of SORCodes without the descriptions, units etc. How can I cycle through
every record doing a DLookup and updating the appropriate fields (the fields
I'm updating automatically in the form? I'm sure it's a simple VBA procedure
but I haven't quite got the hang of recordsets etc.

Thanks

Dave

Ofer Cohen said:
First use IsNull instead of = Null

Try

If IsNull(Me![SORCode]) Then
Me![Description] = DLookup("[Description]", "ScheduleOfRates",
"[SORCode] = '" & Me![SORCode] & "'")
Me![Units] = DLookup("[Units]", "ScheduleOfRates", "[SORCode] = '" &
Me![SORCode] & "'")
Me![Price] = DLookup("[Price]", "ScheduleOfRates", "[SORCode] = '" &
Me![SORCode] & "'")

End If

Because the SORCode is a text type field you need to add a single (') before
and after, without it, it will treat it as number
--
Good Luck
BS"D


David M C said:
Private Sub SORCode_AfterUpdate()


If Me![SORCode] = Null Then
'Do Nothing
Else
Me![Description] = DLookup("[Description]", "ScheduleOfRates",
"[SORCode] = " & Me![SORCode])
Me![Units] = DLookup("[Units]", "ScheduleOfRates", "[SORCode] = " &
Me![SORCode])
Me![Price] = DLookup("[Price]", "ScheduleOfRates", "[SORCode] = " &
Me![SORCode])
End If

Me.Requery

End Sub

I have a table, WorksDetail and a table ScheduleOfRates. My form puts
records into the WorksDetail table. For about 25% of the records, we use what
is known as the Schedule of Rates, which is an industry standard system of
pricing. When one of these codes is entered, I'm trying to "auto-populate"
the Description, Units, and Price fields of my form (or write these records
to the appropriate table).

I've tried using the above DLookup code but I get a data type mismatch error.

Where am I going wrong?

ScheduleOfRates:

SORCode : Text
Description : Text
Units : Text
Price : Currency

WorksDetail:

SORCode : Text
Description: Text
Quantity : Number
Units : Text
Price : Currency
Click to expand...
Click to expand...
 
O

Ofer Cohen

Please Back up your data before you try anything

--
Good Luck
BS"D


David M C said:
Great, thanks, got it working.

One more question. With my tanle design change, in WorksDetail I now have a
lot of SORCodes without the descriptions, units etc. How can I cycle through
every record doing a DLookup and updating the appropriate fields (the fields
I'm updating automatically in the form? I'm sure it's a simple VBA procedure
but I haven't quite got the hang of recordsets etc.

Thanks

Dave

Ofer Cohen said:
First use IsNull instead of = Null

Try

If IsNull(Me![SORCode]) Then
Me![Description] = DLookup("[Description]", "ScheduleOfRates",
"[SORCode] = '" & Me![SORCode] & "'")
Me![Units] = DLookup("[Units]", "ScheduleOfRates", "[SORCode] = '" &
Me![SORCode] & "'")
Me![Price] = DLookup("[Price]", "ScheduleOfRates", "[SORCode] = '" &
Me![SORCode] & "'")

End If

Because the SORCode is a text type field you need to add a single (') before
and after, without it, it will treat it as number
--
Good Luck
BS"D


David M C said:
Private Sub SORCode_AfterUpdate()


If Me![SORCode] = Null Then
'Do Nothing
Else
Me![Description] = DLookup("[Description]", "ScheduleOfRates",
"[SORCode] = " & Me![SORCode])
Me![Units] = DLookup("[Units]", "ScheduleOfRates", "[SORCode] = " &
Me![SORCode])
Me![Price] = DLookup("[Price]", "ScheduleOfRates", "[SORCode] = " &
Me![SORCode])
End If

Me.Requery

End Sub

I have a table, WorksDetail and a table ScheduleOfRates. My form puts
records into the WorksDetail table. For about 25% of the records, we use what
is known as the Schedule of Rates, which is an industry standard system of
pricing. When one of these codes is entered, I'm trying to "auto-populate"
the Description, Units, and Price fields of my form (or write these records
to the appropriate table).

I've tried using the above DLookup code but I get a data type mismatch error.

Where am I going wrong?

ScheduleOfRates:

SORCode : Text
Description : Text
Units : Text
Price : Currency

WorksDetail:

SORCode : Text
Description: Text
Quantity : Number
Units : Text
Price : Currency
Click to expand...
Click to expand...
Click to expand...
 
D

David M C

Our company does response maintenance work. Our first client sent response
works over using the Schedule Of Rates (a predetermined code for a specific
task, Gas safety check for instance). We now have 3 or 4 more clients, none
of whom use the Schedule of Rates.

When I first set it up, all the user had to do to enter the works detail was
put in the SORCode. The form would then display the appropriate Description
etc using a query. However, with these new clients, the user needs to be able
to enter their own descriptions of the works to be carried out. I needed a
way to allow the use of the Schedule of Rates AND users own descriptions. If
the rates change, we still need the old records to display the correct
information. This was the only way I could think to go about it.

Ofer Cohen said:
Try ths SQL

UPDATE TableName INNER JOIN ScheduleOfRates ON TableName.SORCodes =
ScheduleOfRates.SORCodes SET TableName.[descriptions] =
ScheduleOfRates.[descriptions]
WHERE (TableName.descriptions) Is Null

You can do the same with the rest of the fields

Though, why do you save this values, it's better to display the values using
a select query instead of updating the exisiting fields, linking the two
tables.
What happen when the description in table ScheduleOfRates changes, do you
need to update the other table?

--
Good Luck
BS"D


David M C said:
Great, thanks, got it working.

One more question. With my tanle design change, in WorksDetail I now have a
lot of SORCodes without the descriptions, units etc. How can I cycle through
every record doing a DLookup and updating the appropriate fields (the fields
I'm updating automatically in the form? I'm sure it's a simple VBA procedure
but I haven't quite got the hang of recordsets etc.

Thanks

Dave

Ofer Cohen said:
First use IsNull instead of = Null

Try

If IsNull(Me![SORCode]) Then
Me![Description] = DLookup("[Description]", "ScheduleOfRates",
"[SORCode] = '" & Me![SORCode] & "'")
Me![Units] = DLookup("[Units]", "ScheduleOfRates", "[SORCode] = '" &
Me![SORCode] & "'")
Me![Price] = DLookup("[Price]", "ScheduleOfRates", "[SORCode] = '" &
Me![SORCode] & "'")

End If

Because the SORCode is a text type field you need to add a single (') before
and after, without it, it will treat it as number
--
Good Luck
BS"D


:

Private Sub SORCode_AfterUpdate()


If Me![SORCode] = Null Then
'Do Nothing
Else
Me![Description] = DLookup("[Description]", "ScheduleOfRates",
"[SORCode] = " & Me![SORCode])
Me![Units] = DLookup("[Units]", "ScheduleOfRates", "[SORCode] = " &
Me![SORCode])
Me![Price] = DLookup("[Price]", "ScheduleOfRates", "[SORCode] = " &
Me![SORCode])
End If

Me.Requery

End Sub

I have a table, WorksDetail and a table ScheduleOfRates. My form puts
records into the WorksDetail table. For about 25% of the records, we use what
is known as the Schedule of Rates, which is an industry standard system of
pricing. When one of these codes is entered, I'm trying to "auto-populate"
the Description, Units, and Price fields of my form (or write these records
to the appropriate table).

I've tried using the above DLookup code but I get a data type mismatch error.

Where am I going wrong?

ScheduleOfRates:

SORCode : Text
Description : Text
Units : Text
Price : Currency

WorksDetail:

SORCode : Text
Description: Text
Quantity : Number
Units : Text
Price : Currency
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pricing Uplift 2
DLookup in Continuous form 0
DLookup Syntex error it is killing me 7
Squaring Up (Surveyors/Estimators) 1
Blank Fields in Report 2
Validation rule using calculated value from another subform 3
autofill in subform 0
Dlookup on form - help please 2

Top