pulling info from another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Private Sub chkCountyCrim_Click()

intSubscript = selectCustomer.ListIndex
If (Me.chkCountyCrim = -1) And (Me.selectCustomer <> 647) Then
charge = charge + m_countyCrim(intSubscript)
profit = profit + m_countyCrim(intSubscript) - VendorPrice

The last line is my problem, I need to pull vendor price from another table
for the county that is in a sub form.

Having major problems trying to figure it out. Any help greatly appreciated.
 
You can use Dlookup to get the VendorPrice, and if no record is found the NZ
function will replace the Null with 0

profit = profit + m_countyCrim(intSubscript) -
NZ(Dlookup("VendorPrice","VendorTableName","county = " &
Forms![FormName]![SubFormControlName].Form![county Field Name]),0)
===========================================
Now, if the country field type is string the use this
profit = profit + m_countyCrim(intSubscript) -
NZ(Dlookup("VendorPrice","VendorTableName","county = '" &
Forms![FormName]![SubFormControlName].Form![county Field Name] & "'"),0)
 
That helps, but i have my main from called applicant and a sun form called
crimsubfrm.

Each county has a numeric id assigned to it, now when the user clicks the
chkCountyCrim its going to update the charge accordingto the the customer
they selected. Profit is going to take whatever is in the profit txt box on
the main form and check the county id box in the crimsubfrm and subtract the
vendor price from the customer price.

I need to make sure it grabs the right price to subtract. I understand what
u said so far, but i wanted to elaborate on exactly what it does, incase you
didnt understand what i needed to do.

Thanks


Ofer said:
You can use Dlookup to get the VendorPrice, and if no record is found the NZ
function will replace the Null with 0

profit = profit + m_countyCrim(intSubscript) -
NZ(Dlookup("VendorPrice","VendorTableName","county = " &
Forms![FormName]![SubFormControlName].Form![county Field Name]),0)
===========================================
Now, if the country field type is string the use this
profit = profit + m_countyCrim(intSubscript) -
NZ(Dlookup("VendorPrice","VendorTableName","county = '" &
Forms![FormName]![SubFormControlName].Form![county Field Name] & "'"),0)

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



Gary said:
Private Sub chkCountyCrim_Click()

intSubscript = selectCustomer.ListIndex
If (Me.chkCountyCrim = -1) And (Me.selectCustomer <> 647) Then
charge = charge + m_countyCrim(intSubscript)
profit = profit + m_countyCrim(intSubscript) - VendorPrice

The last line is my problem, I need to pull vendor price from another table
for the county that is in a sub form.

Having major problems trying to figure it out. Any help greatly appreciated.
Click to expand...
Click to expand...
 
Try this

profit = profit + m_countyCrim(intSubscript) -
NZ(Dlookup("VendorPrice","VendorTableName","[county id]= " &
Forms![applicant]![crimsubfrm].Form![county id]),0)
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



Gary said:
That helps, but i have my main from called applicant and a sun form called
crimsubfrm.

Each county has a numeric id assigned to it, now when the user clicks the
chkCountyCrim its going to update the charge accordingto the the customer
they selected. Profit is going to take whatever is in the profit txt box on
the main form and check the county id box in the crimsubfrm and subtract the
vendor price from the customer price.

I need to make sure it grabs the right price to subtract. I understand what
u said so far, but i wanted to elaborate on exactly what it does, incase you
didnt understand what i needed to do.

Thanks


Ofer said:
You can use Dlookup to get the VendorPrice, and if no record is found the NZ
function will replace the Null with 0

profit = profit + m_countyCrim(intSubscript) -
NZ(Dlookup("VendorPrice","VendorTableName","county = " &
Forms![FormName]![SubFormControlName].Form![county Field Name]),0)
===========================================
Now, if the country field type is string the use this
profit = profit + m_countyCrim(intSubscript) -
NZ(Dlookup("VendorPrice","VendorTableName","county = '" &
Forms![FormName]![SubFormControlName].Form![county Field Name] & "'"),0)

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



Gary said:
Private Sub chkCountyCrim_Click()

intSubscript = selectCustomer.ListIndex
If (Me.chkCountyCrim = -1) And (Me.selectCustomer <> 647) Then
charge = charge + m_countyCrim(intSubscript)
profit = profit + m_countyCrim(intSubscript) - VendorPrice

The last line is my problem, I need to pull vendor price from another table
for the county that is in a sub form.

Having major problems trying to figure it out. Any help greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
I am getting an error saying it cant find the field crimsubfrm, when i check
the county box in the main form.

Would it be eaiser if I updated the profit box from the subform. i would
have to pull the customers price from the customer that was selected in the
main from and just read the county id and subtract the price of it with the
customers price and uodate the profit.

Thanks for the help so far


Ofer said:
Try this

profit = profit + m_countyCrim(intSubscript) -
NZ(Dlookup("VendorPrice","VendorTableName","[county id]= " &
Forms![applicant]![crimsubfrm].Form![county id]),0)
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



Gary said:
That helps, but i have my main from called applicant and a sun form called
crimsubfrm.

Each county has a numeric id assigned to it, now when the user clicks the
chkCountyCrim its going to update the charge accordingto the the customer
they selected. Profit is going to take whatever is in the profit txt box on
the main form and check the county id box in the crimsubfrm and subtract the
vendor price from the customer price.

I need to make sure it grabs the right price to subtract. I understand what
u said so far, but i wanted to elaborate on exactly what it does, incase you
didnt understand what i needed to do.

Thanks


Ofer said:
You can use Dlookup to get the VendorPrice, and if no record is found the NZ
function will replace the Null with 0

profit = profit + m_countyCrim(intSubscript) -
NZ(Dlookup("VendorPrice","VendorTableName","county = " &
Forms![FormName]![SubFormControlName].Form![county Field Name]),0)
===========================================
Now, if the country field type is string the use this
profit = profit + m_countyCrim(intSubscript) -
NZ(Dlookup("VendorPrice","VendorTableName","county = '" &
Forms![FormName]![SubFormControlName].Form![county Field Name] & "'"),0)

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



:

Private Sub chkCountyCrim_Click()

intSubscript = selectCustomer.ListIndex
If (Me.chkCountyCrim = -1) And (Me.selectCustomer <> 647) Then
charge = charge + m_countyCrim(intSubscript)
profit = profit + m_countyCrim(intSubscript) - VendorPrice

The last line is my problem, I need to pull vendor price from another table
for the county that is in a sub form.

Having major problems trying to figure it out. Any help greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top