Solved Dlookup Or SetValue not working

[hrdpgajjar]

Hi all,

I have a table "ProductT" which has following columns,

- ProductID
- Product Name
- GGRC Price
- Unit

I have created a form named "OrderDetailsF" which get values from table "ProductT".

When i select a product from combo box i need relevance price in Price field for this, I have assigned product price to "SetValue" macro on "After Update" event as attached screen shot. it works perfectly.

now i need product Unit in the unit field same as product price.
For this i have tried to do "SetValue' method but not works,
I have also tried "Dlookup" as =DLookUp("Unit","[ProductT]","[ProductID]=" & "ProductName")

but it does not work.


What i am doing wrong?

Please help.

below is the download link for my database,

Farmer Data Entry Management System

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Thank You,


- Hardip

 

[mike60smart]

Hi all,

I have a table "ProductT" which has following columns,

- ProductID
- Product Name
- GGRC Price
- Unit

I have created a form named "OrderDetailsF" which get values from table "ProductT".

When i select a product from combo box i need relevance price in Price field for this, I have assigned product price to "SetValue" macro on "After Update" event as attached screen shot. it works perfectly.

now i need product Unit in the unit field same as product price.
For this i have tried to do "SetValue' method but not works,
I have also tried "Dlookup" as =DLookUp("Unit","[ProductT]","[ProductID]=" & "ProductName")

but it does not work.


What i am doing wrong?

Please help.

below is the download link for my database,

Farmer Data Entry Management System

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Thank You,


- Hardip

Hi
If you are going to upload your database then you need to include all of the Forms that relate to your tables.

 

[DHookom]

Most of us prefer VBA rather than macros. However, try
=DLookUp("Unit","[ProductT]","[ProductID]=" & ProductID)
This assumes the control/field in your subform is numeric and named ProductID

 

[LarryE]

Here is an updated file for you so products can be selected for each order. You can also print an invoice for each order, if relevant.
You sent me a personal message asking for more help but never told me what questions you had.

I did make one assumption that customers and farmers were the same, so I used the Farmer table instead of the customer table.

 

[mike60smart]

Hi all,

I have a table "ProductT" which has following columns,

- ProductID
- Product Name
- GGRC Price
- Unit

I have created a form named "OrderDetailsF" which get values from table "ProductT".

When i select a product from combo box i need relevance price in Price field for this, I have assigned product price to "SetValue" macro on "After Update" event as attached screen shot. it works perfectly.

now i need product Unit in the unit field same as product price.
For this i have tried to do "SetValue' method but not works,
I have also tried "Dlookup" as =DLookUp("Unit","[ProductT]","[ProductID]=" & "ProductName")

but it does not work.


What i am doing wrong?

Please help.

below is the download link for my database,

Farmer Data Entry Management System

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Thank You,


- Hardip

Hardip
You currently have an AfterUpdate Event on the Product Combobox which says:-

[GGRC Price] = ProgramName.Column(2)

Note the combobox would normally by named cboProduct and not ProductName.

To get the Unit you just need to add "Unit" to the Combobox Row Source and then add the following to the AfterUpdate :-

Me.Unit = ProgramName.Column(3)

 

[hrdpgajjar]

Hardip
You currently have an AfterUpdate Event on the Product Combobox which says:-

[GGRC Price] = ProgramName.Column(2)

Note the combobox would normally by named cboProduct and not ProductName.

To get the Unit you just need to add "Unit" to the Combobox Row Source and then add the following to the AfterUpdate :-

Me.Unit = ProgramName.Column(3)

I have already added "Unit" in the combobox row source, and changed "ProductName" combobox to "CboProductName", and trying to add one more 'SetValue" for unit as per attached screenshot but no luck. can u help further?

 

[hrdpgajjar]

Hardip
You currently have an AfterUpdate Event on the Product Combobox which says:-

[GGRC Price] = ProgramName.Column(2)

Note the combobox would normally by named cboProduct and not ProductName.

To get the Unit you just need to add "Unit" to the Combobox Row Source and then add the following to the AfterUpdate :-

Me.Unit = ProgramName.Column(3)

You mean to say, I have to convert my Unit Textbox to Combobox ? or i am getting it wrong? Can u elaborate it further?

 

[arnelgp]

see your db

 

[hrdpgajjar]

Here is an updated file for you so products can be selected for each order. You can also print an invoice for each order, if relevant.
You sent me a personal message asking for more help but never told me what questions you had.

I did make one assumption that customers and farmers were the same, so I used the Farmer table instead of the customer table.

Can u pls solve my Unit lookup problem on the file i've uploaded ?

 

[hrdpgajjar]

see your db

Its a great help, Thanks only one question, if i change control source to "ProductIT" does it affect my data? i mean does it stores productID in field instead of product name?

 

[arnelgp]

you should save the productID to your table and only creare a link to your product table to get the product name.

 

[June7]

OrderDetailsF RecordSource is unnecessarily complicated. You are binding product combobox to wrong field. Bind this form to OrderDetailsT table and don't include others.

Should save ProductID not ProductName. Yes, combobox ControlSource should be ProductID (advise not to use exact same name for multiple fields, consider ProductID_FK).
Change BoundColumn to 1.
Change ColumnCount to 4.
Change ColumnWidths to 0";2";0.5";0.5" (first must be 0, others whatever you want)

Don't need DLookup to get Unit. Just reference combobox column by index (index begins with 0): =[ProductName].Column(3)
I name controls different from the fields and use a prefix, such as cbxProduct: =[cbxProduct].Column(3)

Don't really need a query object for combobox RowSource. Put SQL statement directly in RowSource. Can reference table in RowSource as long as order of fields in table is never changed.

I assume you save GGRC Price because value is subject to change in ProductT.

Also advise not to use spaces in names. Limit symbol/punctuation to underscore.

In future, suggest you provide file by attaching to post. If necessary, zip with Windows Compression.

 

[hrdpgajjar]

you should save the productID to your table and only creare a link to your product table to get the product name.

Tried it, this thing records all product names to my product table, e.g. if i select a product name and don't enter quantity then it stores it without price in my product table. I dont want to store values in my product table. i want to use it in my "OrderF" only. ProductT must be stay as it is. no change.

 

[arnelgp]

here i added OrderLineQ query and use it as Recordsource of your form.
it will not save the Price to your Product table, only in your OrderDetail table.

 

[June7]

Did you read post 12?

 

[hrdpgajjar]

here i added OrderLineQ query and use it as Recordsource of your form.
it will not save the Price to your Product table, only in your OrderDetail table.

didn't it the same thing done in my "OrderdetailsQ" as well? i mean what is the change ?

 

[hrdpgajjar]

OrderDetailsF RecordSource is unnecessarily complicated. You are binding product combobox to wrong field. Bind this form to OrderDetailsT table and don't include others.

Should save ProductID not ProductName. Yes, combobox ControlSource should be ProductID (advise not to use exact same name for multiple fields, consider ProductID_FK).
Change BoundColumn to 1.
Change ColumnCount to 4.
Change ColumnWidths to 0";2";0.5";0.5" (first must be 0, others whatever you want)

Don't need DLookup to get Unit. Just reference combobox column by index (index begins with 0): =[ProductName].Column(3)
I name controls different from the fields and use a prefix, such as cbxProduct: =[cbxProduct].Column(3)

Don't really need a query object for combobox RowSource. Put SQL statement directly in RowSource. Can reference table in RowSource as long as order of fields in table is never changed.

I assume you save GGRC Price because value is subject to change in ProductT.

Also advise not to use spaces in names. Limit symbol/punctuation to underscore.

In future, suggest you provide file by attaching to post. If necessary, zip with Windows Compression.

This works finally... thanks man

 

[mike60smart]

This works finally... thanks man

Here are your Forms reworked