Replies

It depends on how your data is organized. If you have invoices (with their invoice numbers) in one table and the items on each invoice in another then you may have to do two finds. If invoice number and item number are in the same table then one find will do:

Dim db As Database
Dim recordset As Recordset

Set db = Currentdb()
Set recordset = db.OpenRecordset(“invoice_item_table”)
recordset.FindFirst “invoicenbr = ” & [Search Invoice Number] & ” And itemnbr = ” & [Search Item Number]

[Search Invoice Number] and [Search Item Number] are, of course, whatever fields are holding the invoice number and item number that you are searching for.

I checked the Microsoft Knowledge Base and found that, “for backwards compatibility”, it uses Excel 5.0 when you do “Analyze with Excel”. Also, it implies that there is no fix for this, but gives three “workarounds”. You can refer to Microsoft Knowledge Base article number Q201589 for more information.

Edited by Charlotte to activate link

As long as you don’t use any features new in Access2000 you can convert an Access2000 database back to an Access97 database at any time. The only thing you CAN’T convert back is an mde (compiled database) file. Any MDB will work just fine, though.

Try adding an event procedure in the OnAfterUpdate event for the form that does the following VB coding:

Me.SALN.Requery

This will rerun the query that is acting as the RowSource for the SALN combo box.

I’d be glad to look at your database, however, I only have Access97 available to me right now. Could you please convert your database to Access97 (don’t worry, it makes a new copy of the database and leaves your Access2000 database intact) and send me the Access97 database?

As a last resort I could do that, but there is SOME information on the page header that I’d like to have printed on each ReportFooter page. I could include the information at the top of the ReportFooter section, but it would only print for the first ReportFooter page.

Thanks for the suggestion.

Do you call the function from the OnClick event for the listbox? Click on the listbox and select Properties. In the Event properties there should be something in the OnClick event to call the function. If not, that’s why your coding is not executing. Also, I don’t know if putting the function call in your OnClick event is the best thing to do. What happens if someone clicks on the same order twice? You’ll update inventory twice!

It would be better to let you select an entry from the listbox, then press a command button whose OnClick event points to your function. After processing your function, you should somehow remove that order from the listbox to prevent selecting it a second time.

Let me see if I understand what you are trying to do:

You have a table (Products) of items (ProductID) that contains the onhand inventory (UnitsInStock) of those items. You also have a listbox (OrdersList) containing a list of orders (OrderID) backed by a table (Order Details) containing the items on that order. If you select an order from OrdersList you want to update Products for those items contained in the order’s [Order Details] by adding the Quantity in [Order Details] to the UnitsInStock in Products.

The SQL statement that your friend gave you is essentially correct. What you need to know is how to limit the query to only the selected order. The MyOrder variable should contain the value of the OrderID that was selected from the listbox.

When using a listbox, you can’t directly refer to the selected value. You have to search through the ListBox’s ItemsSelected collection because a listbox can have more than one row selected. The ItemsSelected collection contains a list of the row numbers that are highlighted in the ListBox.

here’s the coding that I think you need:

Public Function UpdateStock()

Dim strSQL As String
Dim varRow As Variant

For Each varRow In Me.OrdersList.ItemsSelected
strSQL = “UPDATE Products RIGHT JOIN [Order Details] ON products.Productid = ” _
& “[Order Details].ProductID SET Products.UnitsInStock = ” _
& “[Quantity]+[UnitsInStock] ” _
& “WHERE [Order Details].OrderID= ” & Me.OrdersList.ItemData(varRow) & “;”
DoCmd.RunSQL strSQL
Next varRow

The good thing about this is you can define your listbox as multi-select and let the user select more than one order at a time if you wish. By the way, the above coding assumes that OrderID is numeric. If it is alpha, then you have to put single quotes before and after the double-quotes:

‘” & Me.OrdersList.ItemData(varRow) & “‘;”

Hope this helps.

If you already have a query that extracts ONE person’s transactions, I assume that you imbedded criteria in a query to do this. You then export the query results to Excel.

If you are kicking this process off with a form, try adding a field (it could be hidden) to the form to hold the current user’s name. Next, change the criteria in the query to use the form field’s contents as the criteria. For example:

Forms!frmEmailTransactions!CurrentUser

Then, if you have a table of the usres, walk through the table and, for each user, put that user’s name in the form field. Do a TransferSpreadsheet using the query as the source table. Imbed the user’s name somewhere in the Excel spreadsheet name. The query will export the transactions for the current user to an Excel spreadsheet. For example:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, qryCurrentUsersTransactions, Me.CurrentUser & “‘s Transactions.xls”, True

In the above example, you export to Excel97 the results of running your query (qryCurrentUsersTransactions) and create a file named “abcd’s Transactions.xls” where abcd is the name of the current user.

The OutputTo action is the same as if you chose the “Analyze with Excel” button on the toolbar. This creates an Excel spreadsheet with formatted columns (including formatted column headings). If you do not want the formatted column headings, try using TransferSpreadsheet instead.

You may want to try the line drawing commands in VBA. You can use them to draw lines of any width and length and to start them at any position on the printed page (not just in the detail section). I use this to draw vertical lines that extend from the column headings to the bottom of the page, regardless of where the last detail line on the page was printed. Look for help on LineDraw.

You can use Convert Database under Database Utilities to convert the database back to Access 97. However, you may not be able to share the same database between the two versions of Access at the same time. There is some documentation in the Access help files about this.

The Nz function converts Null strings to zero-length strings! You may want to eliminate the Nz function and let the Null value (if the field is empty) go to the table field (provided the field is not indexed and allows null values).

If you really wanted to get into the 21st century, try a wireless network! You can buy a wireless NIC with a PCI adapter for your desktop and a wireless PC card for your laptop. Provided the AT&T@Home is connected to your desktop computer, this not only gives you the ability to transfer data between the desktop and laptop, but gives your laptop Internet access from anywhere in the house! You don’t have to bother with a wireless access point. If your desktop is Windows 98 or above you can use Microsoft’s Internet Sharing. Otherwise, get the free (or low-priced, depending on options) Wingate Server software from Wingate. The total cost will be less than $200. With deals, it could get down to about $150.

If you are intent on doing this with reports, you can do the following:

1. Create a report with no ReportHeader, PageHeader, GroupHeader, GroupFooter, PageFooter or ReportFooter (i.e. only a Detail section). Map your fields in the detail section so you get multiple rows of data to suit your purposes. Save this report.

2. There is a toolbar button that you can use to run the report to Word or Excel. You can add an entry to this toolbar button to output to Notepad. First, right-click on the Database toolbar and choose Customize from the shortcut menu. Click on the Commands tab and you’ll see all of the available commands that can be added to your menus and toolbars. In the Categories list box, select Tools. Now, locate the Output To Notepad button in the Commands list box and drag it to the desired position on the Database toolbar. Then click Close.

Now, whenever you want to create your ASCII file, simply click on the report name, then click the dropdown to the right of the OutputTo toolbar button and choose Output To Notepad!