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

I have the same question (0)

All responses (8)

Answers (0)

Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

: ) 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?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Suggested answer

Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Suggested answer

Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Arunsh 606 on at

Like (0)

Report

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

Was this reply helpful? Yes No