query criteria

[MAAS8000]

Hello,
this my first post here
I have a source table with fields: item, manufacturer & supplier.
every item have more than one manufacturer and more than one supplier.
i have another table for purchase order, the same fields are available also, I made lookup for the field item from the source table.
The question: I need to limit the selection in the fields of manufacturer & supplier according to what I select in the item field
I MADE THIS CODE, I have tried to make criteria in the lookup query but i have message every time to enter the (PO.item) value
i need the query to reed it automatically

SELECT source.manufacturer_s, source.item_s FROM source WHERE (((source.item_s)=[PO]![item]));

thank you

 

[arnelgp]

you need to use form as your data entry for your po.
use combobox for manufacturer and supplier.

for combobox the Rowsource is:
select manufacturer_s from source where items_s=forms!yourFormName!item

for supplier is same:
select supplier_s from source where items_s=forms!yourFormName!item

remember to add code on both manufacturer and supplier texboxes GotFocus Event:

Private sub supplier_GotFocus()
Me.cboSupplier.Requery
End Sub

Private sub manufacturer_GotFocus()
Me.cboManufacturer.Requery
End Sub

 

[MAAS8000]

thank you
i got it, but i have more questions
1- the selection appears duplicates i.e. if the item is recorded more than one time it will appear during selection duplicated.
2- i need to clear the data of the supplier and manufacturer if i changed the item
thank you again

 

[arnelgp]

Add code to the item AfterUpdate Event:

Private Sub item_AfterUpdate()
Me.cboSupplier=""
Me.cboManufacturer=""
End Sub



On duplicate appearing in combo, use

Select Distinct supplier_s ....
Select Distinct manuacturer ...

 

[MAAS8000]

OK
I made a query to append the finished P.O. in another table to store all finished P.O. in it, but i need to give the P.O. a unique number to be as a P.O. number

 

[arnelgp]

you mean you need a PO generator?

Public Function GeneratePO() as string
GeneratePO = Val(Nz(DMax("POField", "POTable", "Left(POField, 4)=Year(Date())"), Year(Date()) & "0"))+1 & ""
End Function

 

[MAAS8000]

thank you again
you open the gate for me but i found another way.

another problem,
1-In the entry form the access save the entry always on closing or pressing new,
I need saving to be after pressing save only

2-I run some macros like delete records or append to a table, i want the confirmation massage to be "OK" automatically

 

[MAAS8000]

Ok
I will do