VB Customisation to check value against database table

(0) Share

Report

Posted on by Community Member

Hello

I'm wanting to check if I have prices in system against items that I would raise a purchase order for.

I'm thinking a VB editor modification could do this for me.

What I basically want to do is, as the user enter all header info on below window and move down to Item Number and type in an Item number and press tab or move to next cell, check if that Item Number has a 'Price' against it for that Creditor ID.

There is a table in database that I could check against (with columns CREDITORID, ITEMNMBR, PRICE)

My problem is, I don't know how to add this scrolling window bit (grid) to VB Modifier,

plus I'm not very sure how to write a code to this Item Number field to check against database table.

it'd be great if someone could help.

Thanks

*This post is locked for comments

All responses (8)

Answers (0)

Arunsh 606 on at

Like (0)

Report

RE: VB Customisation to check value against database table

Hi did you add the grid field to VB. I am to trying but not able to do it.
Suggested answer

Almas Mahfooz 11,006 User Group Leader on at

Like (0)

Report

RE: VB Customisation to check value against database table

You have to add window in vba and then select 'Add Fields to visual basic' then a small square box with plus sign will appear and you have to click on item number field. it will add fields in VBA.
Community Member on at

Like (0)

Report

RE: VB Customisation to check value against database table

Hi,

below is the code page for window of same screen , if i want to add any fields to this I can simply go to Screen and add whichever fields I want using "Add Fields to Visual basic", as I have done as seen below.

Below is the code page for grid of the screen I want to modify

the Field "Item Number" to which I want to write code on is on this grid I believe?

Now I cant add fields that are inside Grid to VB editor using "Add Fields to Visual basic" option. How do I add them?
Suggested answer

Almas Mahfooz 11,006 User Group Leader on at

Like (0)

Report

RE: VB Customisation to check value against database table

oh you mean that, yes you are right. :) no you have to add grid separately. and then on item number change you have to read item price table and for that you have do some vba coding.
Community Member on at

Like (0)

Report

RE: VB Customisation to check value against database table

I can see below in VB editor

I believe this is the grid part on a window? Probably it added itself when I added the window to vb editor. Now is there any way to add its fields to this modifier?
Suggested answer

Almas Mahfooz 11,006 User Group Leader on at

Like (0)

Report

RE: VB Customisation to check value against database table

you can not add grid, all you have to do is to make a database connection to read the table execute a query to check the price against the entered item and based on it show a message box.
Community Member on at

Like (0)

Report

RE: VB Customisation to check value against database table

Hi

It's just because I dont know how to add the grid on this window to VB editor to actually start writing any code.

I've written small bits and pieces of code in this VB editor, but I haven't done anything to do with referencing to database tables. I've had a lot of help reading through the VBA Developers Guide.

No, I wont be doing any other checks.

I want to do this just as a check to acknowledge user if prices exist for a part or not when users raise an order. ideally, a message box popping up if no prices against an Item

thanks
Almas Mahfooz 11,006 User Group Leader on at

Like (0)

Report

RE: VB Customisation to check value against database table

: ) it's a whole vba customization not just a help, I would love you help but can not provide you a whole code as it will take time to create as per your requirement. Dynamics Community is too humble otherwise questions of such kind get down-voted at StakeOverflow. what will you do after checking the prices? will their be further step on the basis of result you get?