BA code to add data in the listbox that meets certain criteria

[aman]

VBA code to add data in the listbox that meets certain criteria

Hi Guys

I have a Access table that stores BatchNo,Scandate,NewBatchNo . As I can't attach Access database here so I have exported data from Access to Excel in Sheet1 to give you the idea how the data is stored in Access table.

Now I have to write code in VBA that will check the last NewBatchNo in the table . In the attached workbook its 194389. Now vba code should check the NewBatchNo which are blank before 194389 . Now in the records where NewBatchNo is blank , it should add the corresponding BatchNo,ScanDate in the listbox1 in form1.

e.g Sheet2 in the attached workbook stores Bathcno and Scandate of those records where NewBatchNo is null and before the Last used NewBatchNo which is 194389 in our example.

ANy help would be much appreciated.

Thanks
​

 

[TimW]

Hi
IMHO You are not entirely clear what you want to achieve here.
For instance a null new batch number before 194389. A null is not a value to BEFORE in what terms - date?
Also, if the forms are linked you would not need to link them using VBA.

Sorry but if i could help I would need a better description of what you are trying to do and why use VBA to link the forms

Kind regards

T

 

[GinaWhipp]

Not sure I am understanding exactly what your goal is. Why is it you cannot upload your database? You can take out any confidential information but I think it would go a long way as to helping us understand what you are trying to accomplish.

 

[aman]

Hi Gina, I can't upload access database because of company security restrictions. But I will try to explain again. I want to check the last used NewBatchNo in the table and then before that NewBatchNo, I have to check the NewBatchNo which is blank. To all the records where NewBatchNo is blank , add corresponding BatchNo and ScanDate in the listbox using VBA.

Any help would be much appreciated.
Thanks

 

[GinaWhipp]

Getting both values can be done via a query. The second part, adding a List Box, can be done by changing the List Boxes Row Source to...

SELECT YourTable.BatchNo, YourTable.Scandate, YourTable.NewBatchNo
FROM YourTable
WHERE (((YourTable.NewBatchNo)="" Or (YourTable.NewBatchNo) Is Null));

So, I guess I'm not sure why you need VBA TO DO THIS. Are you setting the List Box Row Source initially via VBA?