iainmacleod
asked on
VBA to look through table and find record that two fields have certain criteria
Hello,
I have a form that i need to display a record on open.
This record is from a table with a column "ID" There maybe duplicate ID numbers but i need the code to find the ID and if the "servicecode" field has certain text ("Discount" for instance) then to show that record. I tried the following but I have got something wrong as it does not search for the combination.
If invs!ID = Forms!InvoiceAlterView!Inv oicedetail sAlterView .Form!invd etailID And Left(invs!servicecode, 10) = "Discounted" Then
Have I explained this....????
I have a form that i need to display a record on open.
This record is from a table with a column "ID" There maybe duplicate ID numbers but i need the code to find the ID and if the "servicecode" field has certain text ("Discount" for instance) then to show that record. I tried the following but I have got something wrong as it does not search for the combination.
If invs!ID = Forms!InvoiceAlterView!Inv
Have I explained this....????
ASKER
Thanks for your quick reply.
That still does not find the record, however I think there is a more fundamental error in my process.
I will have to review it and repost.
Thanks
That still does not find the record, however I think there is a more fundamental error in my process.
I will have to review it and repost.
Thanks
ASKER
I actually think the stem of this problem is that Forms!InvoiceAlterView!Inv oicedetail sAlterView is a continuous form and when I am doing my IF statement it is finding the first INVDETAILID on the form not necessarily the record that I have clicked on....
If you are using a continuous form to open records on a different form, the continuous form MUST include the uniqueID of the records it is displaying. Otherwise, how will you get the other form to open to the record you want?
<when I am doing my IF statement it is finding the first INVDETAILID on the form not necessarily the record that I have clicked on.... >
try saving the Record information to variables after you have selected the record, then refer to this variables in your IF statement..
what version of access are you using?
try saving the Record information to variables after you have selected the record, then refer to this variables in your IF statement..
what version of access are you using?
ASKER
Thanks all. Using 2010.
I am looking at redesigning the way I have the forms setup.
I am looking at redesigning the way I have the forms setup.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If invs!ID = Forms!InvoiceAlterView!Inv