Replies

In the form properties in design view go to the data tab and change the allow additions to no.

If you look at the properties and methods of the listbox object in the online help you should be able to figure this out. Specifically look at the ItmesSelected collection and the ItemData property. With these two you should be able to loop through the collection using a For Each…Next statement and return the selected item with the ItemData property. Here is an example from the online help in Access97 (my 2002 machine is offline at the moment):

Sub BoundData()
Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Forms!Contacts
Set ctl = frm!Names
For Each varItm In ctl.ItemsSelected
Debug.Print ctl.ItemData(varItm)
Next varItm
End Sub

Replace the object names with the names of your form and listbox. Then, instead of debug.print, concatenate the selected values to build your where string.

You will need to build a criteria string or where clause for your report while looping through the items selected collection of your listbox. At the end you should have something that resembles “[factory] = “SomeID” AND [line] = “LineID01” AND [date] = #SomeDate# OR [factory] = “SomeID” AND [line] = “LineID02” AND [date] = #SomeDate# OR [factory] = “SomeID” AND [line] = “LineID03″ AND [date] = #SomeDate#;” You can then use this string in the where argument of the OpenReport method to filter the records displayed in your report. This is just an example to get you started in the right direction. Hope it helps!

What if you create a new db in 2000 and link to oracle tables and update them. Does it happen then?

I’ve updated 97 db’s to xp but not with links to oracle but sql server. I would recommend getting the latest odbc drivers for oracle and try deleting and recreating the links to the oracle tables.

You might also try specifying the recordset type such as dbopendynaset. This has solved some of my error messages when I’ve upgraded from 97 to XP.

A lot depends on what you intend to do with the data once it is in the db. I used Access 2.0 with tables consisting of record counts in the 100,000’s. But all I did was simple reporting and filtering. For more complex tasks I would definitely recommend SQL Server would be your best bet and may be worth the effort in the long run.

In a private message you wrote:

“I have one question how do i get a running tally of my Inventory. I have the number for example one item 3, 9, -3. How do I get the totals for that item to come out 9 and not show all three entries. I tried SUM but of course that wouldn’t work cause it tries to find same entries. Could you help me out. Is it a D-Sum?? I’m not too sure.”

Which column describes or identifies the item you need to sum? You should be able to create a query where you group by this column and sum on the amount column.

Instead of 3 tables I would use 1 or 2 and use some columns to specify the types of transactions. This way you could set rules that a to stocj transaction uses positive number for a to stock transaction and a negative number for a from stock transaction. Then you could build a totals query and use sum to get your quantity in stock. Unless there’s something I’m missing?

In your query, change the join between the tables from an equi-join to right join – all records from accounts table and only those that match from transactions. You should then get records returned.

Right click on the join line, select join properties and then choice 2 should be the one you need.

Yes, performance was improved with SQL server. I’m still monitoring it though and am currently researching replication.

I’m not sure about your first question. I’ll see if I can find out though.

In which row of the query grid are you referencing the combobox? It should go in the criteria row under the department code column.

Here’s a couple of links that hopefully will help.

KB

Experts

You can try and use the attached code if you know what length the result string should be. Usage is as follows:

varString = PadZero(“StringToBePadded”,10)

where 10 is the length of the result string.

Are the front ends located on workstations or servers? Make sure your tables have good indexes. Also, when returning recordsets, try and limit the number of records being returned with criteria. This should improve performance greatly if you are using large recordsets. I have one multi-office application which I upsized to SQL server. This is not for the faint of heart, though. But it is something you might want to look into.