Confusing DB question... - Xtreme Visual Basic Talk

05-12-2003, 01:11 AM

alvifarooq alvifarooq is offline

Junior Contributor

Join Date: Mar 2003

Posts: 205

Confusing DB question...

hey guys...

ok see the attachments first for this...i have a picture of two tables that i have in access...

The user would select an Slp given in tblChemicalList after which we would only be concerned with that recordset...say the user selects S132100 thwn we would have that entire row as our chosen recordset...

If you see my code I try to compare then the fields of tblchemicallist starting from .Fields(2) that is HCl and compare each opf these fields with the values in the Chemical_Name column in tblDrugInfo...whereever i get an exact match I subtract the quantity in tblChemicalList from the one in tblDrugInfo under Current_Quantity...

I am not getting the code to wkr cause it gives me the eof bof error...plz check...

if u havent noticed yet this is kind of an inventory update system for chemicals in a lab...

dont pay heed to teh value cause i juggled arnd with many of em...check if i have the right logic!

Thanks...

Farooq.

Attached Images

Tables.jpg (424.1 KB, 3 views)

Attached Files

Code.txt (948 Bytes, 2 views)

05-12-2003, 09:27 AM

Big Boabby Big Boabby is offline

Junior Contributor

Join Date: Apr 2002

Location: Scotland

Posts: 275

Hi

Your code:

Code:

If Val(rsDrugInfo!Chemical_Name) = Val(rsChemicalList(Number).Name) Then
         rsDrugInfo!Curr_Quantity = Val(rsDrugInfo!Curr_Quantity) - Val(rsChemicalList!Fields(Number))
end if

I think the problem here is that you are trying to reference a value in rsChemicalList were in actual fact it is a field name. I have not tried any alternative syntax but I think it should go something like this:

Code:

If Val(rsDrugInfo!Chemical_Name) = rsChemicalList.Fields(Number).Name Then
	rsDrugInfo!Curr_Quantity = Val(rsDrugInfo!Curr_Quantity) - Val(rsChemicalList!Fields(Number))
end if

Hope this helps

__________________
As we sit bousin' at the nappy,
an' gettin fou, o unco happy

05-12-2003, 06:50 PM

alvifarooq alvifarooq is offline

Junior Contributor

Join Date: Mar 2003

Posts: 205

thanks...

i tried that code but it didn't do the trick...actually the problem with the if statement is that it is using chemicallist.fields(number).name and in the preceding sql statement i write:

sql2 = "SELECT ID, Slp FROM tblChemicalList WHERE Slp = '" & Tag & "' ORDER BY ID"

so there is no selection made for the fields(number) value...I get an error:

Item cannot be found in the collection

aside from that...teh subtraction is not carried out because no value exists for Val(rsChemicalList!Fields(Number)) for a similar reason...if anyone can help me answer these two specific queries, I would be grateful...

I think I just have to change the sql statement format to include all fields or smth...

Farooq.

05-12-2003, 07:27 PM

alvifarooq alvifarooq is offline

Junior Contributor

Join Date: Mar 2003

Posts: 205

answer...

ok so what i do is simply use this sql query:

sql2 = "SELECT * FROM tblChemicalList WHERE Slp = '" & Tag & "' ORDER BY ID"

all fields are selected and i can easily loop through...

Thanks a lot all ppl...

Farooq.