Multiple criteria

[Lighty_11]

Hi All,

I am designing a DB containing website orders. At the moment if I want to update records to show orders as posted, I have the order_reference field prompt me for a perameter value and I update one order number at a time.

What I aim to do is create a query that searches for multiple order numbers at a time and presents all the fields for these records in a continuous form so I can update post dates in a single sweep.

My idea is more conceptual at this stage rather than functional but just wondered if there was a solution out there to help me on my way?

Thanks

 

[Guus2005]

you can use a form with a multiselect listbox to select all possible values you want to see.
when you have made the selection you can create the query in code.
The query must look like this:

select * from Table1
where Id in (1,2,3,4,5)

where 1,2,3,4 and 5 is the selection you made in the multi select listbox.

In the sample databases provided by M$, Nothwind, Solutions and Orders you will find sample forms.

 

[Lighty_11]

Thanks Guus,

I have briefly looked at this with some success and although this will give me a solution, in reality it would not be practical. We are looking at handling maybe 1000 orders per month, where some orders coming from sub-sites will use a different format order number. With the variations and quantity I really want to be able to input the reference directly. We actually use a barcode scanner to scan our order numbers which are imprinted on our despatch notes which I would like to use for the input.

Perhaps I am asking the wrong question......Is there a lookup function I could use on a continuous form so I could enter the order_reference in one field to 'retrieve' the data from my other fields so I could then update the records, repeating this down the page?

 

[Guus2005]

You are using a barcode scanner to scan ordernumbers.
Each time a barcode is scanned an event is triggered (the scanning of a barcode)
In that event you can build up the query of the continuous form.

Another way is do it is to store the barcode (ordernumber) in a table.
The continuous form has a query that might looks somewhat like this:

select * from Table1 inner join ScannedOrderNumber on Table1.OrderNumber = ScannedOrderNumber.OrderNumber

After the scan event you can requery the continous form to display the additional Ordernumber and its information.

The last solution is straightforward, neater and easier to understand.

HTH

 

[Lighty_11]

Right, i've managed to get the table to populate a form with the additional fields relating to the order numbers i've scanned.....Perfect.

However......I need to be able to update these fields to add order notes, change the post details but I can't - all the records seem to be locked. I have checked every option I can think of but can't fidn anything that will fix it.

 

[Guus2005]

Because you are using a join in the forms rowsource you can't edit the fields on the form.
Here are two ways to solve this.
1. You could create a new form specifically designed to edit a single record.
2. Use an unbound form to display the values of the recordset you are using and add a save button to save the changes in the form.

Neither one is better and they are both a lot of work.

Make your choice and get to work.

HTH

 

[Lighty_11]

Cheers Guus, you've helped me loads.......

I did found a solution myself....

It turns out that because my ScannedOrderNumber field wasn't indexed, it would not allow the related record to be edited. Once I changed it to "Yes (No Duplicates)" it has now allowed me to edit each and every record that I scan.

Stumbled across it by accident but so far so good it is working perfectly