Posts by Zzyzzyth

I am trying to send an excel file to one of my customers, and when they try to open it, they get a read-only copy. We've tried "Save as..", closing it and re-opening it, and it still comes up read-only. This is actually one of the few files our customers use that DOES NOT have macros, so it shouldn't have anything to do with security settings. Any ideas?

Re: Normalization

I would say that it is unusual to the tune of about 2-3 of 2,000 shipments per month. But, not unusual in the respect that I can count on a few per month to happen. I am actually still in the design phase, so I wanted to address it now before the database went into use. I contemplated a general "contacts" database where each record was identified with a ContactTypeID. But, wasn't convinced that is the way to go. I may just end up adding them to each table anyway, it just doesn't seem right that way either.

Any other suggestions would be greatly appreciated.

(Sorry about the delay in the response, we are short-handed today, so I'm putting in some warehouse time and can only check back occasionally.)

I'm stumped. I have a typical small company database designed for a transportation company. I have the tables busted out as follows:

Customers
Carriers
Consignees
Shipments
Order Details
as well as a few others. But those are the main tables involved in my dilema.

Our Orders table, as it stands right now, has foreign keys linked to the Carrier that hauls the product, the Customer that the shipment gets billed to, and the consignee that the product arrives at.

I encountered a problem the other day where we actually delivered a shipment for one of our carriers to one of our customers. So, the carrier has become the customer, and the customer became the consignee.

How do I avoid duplicating the two entities into two different tables? I can't use the primary keys assigned to them because they have been used in the contradicting tables already. Any help would be appreciated, let me know if you need more info. Thank you.

Re: Function Return #VALUE!

I think you hit the nail on the head, Dave. In my Application.WorksheetFunction.Vlookup I had designated TRUE when the list wasn't sorted. I changed it to FALSE AND sorted the list, and that seemed to clear it up. Sometimes the simplest things, huh? You look so hard for something so subtle. Thank you again.

Re: Code Causing Function To Crash?

Actually, while waiting for a response, I tried a few things and finally got it to work. All I did was remove the function and place it in another module. Would this make a difference? I wouldn't think so, but I'm learning everyday. It doesn't seem like having the function in the same module as the code would matter, in fact, I do this in other workbooks without a problem. I appreciate everyone's help.

Re: Code Causing Function To Crash?

It's really weird. Basically, it is an invoice template. Once the data has been imported, the function kicks in and calculates the line items. The old module would create a "Copy & PasteAsValues" workbook that we e-mail to our customer. That has worked fine for a year now. All I really did was add code to post the values to a database. When I step through the code, right in the middle of execution it starts recalculating the function again, than returns #VALUE and then I get a mismatch error. I tried turning calculation off at the beginning of the module, and that succeeded in retaining the values long enough to get them to the database, but once I turn calculation back on, it re-calcs the worksheet and I get the #VALUEs again. Really strange.

I have a workbook with a function that has work just fine for the last year. Basically, it calculates prices of various orders. I have added a module to post the amounts to a database and the formulas all turn to #VALUE. Anybody know what would cause this?

Re: Write to Peachtree with VBA?

Yeah, it seems possible to me, too. I would need to determine the database/table layouts, then a simple append ADO/DAO routine should do it, I would think. This is a simple export of invoice info.

Re: Write to Peachtree with VBA?

Thanks for the reply. I'll try the link. I'll let you know if I come up with anything. I've been searching the internet all day, and can't find a straight answer. Although, I have not been told no, so.... the search continues.

Anyone know if it is possible to write code to post Excel data directly to Peachtree using VBA rather than the export/import process?

Re: Auto-create a record

I don't really care for the datasheet view either, but the boss wants to be able to see all shipments at once. I would like to have a subdatasheet-type view within a form if thats possible. He generally just wants to know we have 2 pallets for Houston, 3 pallets for LAX, etc. If I could just show the toals with a + next to them to expand to the details would be cool. Don't know if this would be good for a datagrid or not. I started to try that way, but couldn't get it to cooperate either. I'm kinda learning Access on the fly, which isn't good. I know a lot of little things, but it hasn't quite tied together in my head.

Re: Auto-create a record

Sorry about the delay. I've been trying to get it to work, but it's not firing. I've been trying to avoid using macros. I appreciate you pointing me in the right direction with the append query. This seems to be the way I want to go. My form is in datasheet view, so many rows of orders appear on the screen at one time. When the user enters a destinationID, a lot of the fields auto-fill with dlookup()'s. So, I would like to fire the append query on an After_Update event. Can you tell me how to code that?

Re: Auto-create a record

I'm not sure why, but I can't ever seem to unzip sample files from here. Access kicks them out as an untrusted source. Is there a security setting somewhere that I need to disable? I'd like to see your example.

I have 2 tables: Shipments and Orders. I can have many orders on one shipment.

When the user enters data into the Orders table, I need to check and see if a shipment to this area already exists. I use a temporary key like LAX0831 for routing purposes. If a shipment exists, this new order becomes part of a consolidation with the existing shipment and gets assigned to that ShipID. If a shipment to this area does not exist, I need to create one automatically, otherwise it will not allow me to add the record because of integrity. Shipment info needs to be generated automatically requiring no user intervention. Any suggestions on the best way to go about this?

Re: DLookup in datasheet view

I got it. I had my quotes in the wrong place for one, but mainly DSDID is a text field that could contain "94Z" (among others). I fixed it with IsNumeric().

Maybe this will help someone else.

=DLookUp("[CrossRef]","ZipCIN","[DSDID] = '" & Forms![Temp Orders]!DSDID & "'" & " AND Discount = '" & IIf(IsNumeric([DSDID]),IIf([DSDID]<1000,"S","D"),"B") & "'")

Re: DLookup in datasheet view

Anybody?

You are allowed to throw in an IF statement in a DLookup() aren't you?

The formula below works but returns the first instance of the DSDID, which happens to be a "B" discount. I need it to DLookup(): IF(DSDID<1000, "S", IF(DSDID<100000, "D", "B")).

Can anyone help me, or know of a better way?

=DLookUp("[CrossRef]","ZipCIN","[DSDID] = '" & Forms![Temp Orders]!DSDID & "'")

Re: DLookup in datasheet view

OK. I got it working. However, there are different discounts assigned to the MasterID's and it wants to return the "B" discount no matter what. I need to throw in an IIF([DSDID]<1000,"S",IIF([DSDID]<100000,"D","B")) as the second part of the criteria, but I keep getting a syntax error. I think I'm putting my quotes in the wrong place.

'this part works in returning a field with the "B" discount:
=DLookUp("[ZipCIN].CrossRef","[ZipCIN]","[DSDID]='" & [Forms]![Orders]![DSDID] & '")

'it should be close to this:
=DLookUp("[ZipCIN].CrossRef","[ZipCIN]","[DSDID]='" & [Forms]![Orders]![DSDID] & '" & IIF([DSDID]<1000,"S",IIF([DSDID]<100000,"D","B")))

Can anyone see what I'm doing wrong?

I have a form in datasheet view that I would like one field to be populated based on the value entered in another field. I accidently got this to work one day and for the life of me can't figure out how to do it again. For what it's worth, the dlookup needs to take the value the user enters, retrieve a corresponding (or masterID) field in one table, then use the MasterID to retrieve the data desired from yet another table.

Re: Null a date field in a recordset

'populate the form
Set db1 = DBEngine.OpenDatabase(dbDSD)
Set rs1 = db1.OpenRecordset("SELECT * " & _
                                    "FROM [Vendors] " & _
                                    "WHERE LastOrder < #" & Date - 365 & "#;", dbOpenDynaset)


With rs1
        If Not .EOF Then
            .MoveLast   'populate the recordset
            .MoveFirst  'and fill in the form
            txtVendor.Value = !Name
            txtAddress.Value = !Address
            txtCity.Value = !City
            txtState.Value = !State
            txtZipCode.Value = Left(!ZipCode, 5)
            txtPhone.Value = Format(!Phone, "(000) 000-0000")
            txtFax.Value = Format(!Fax, "(000) 000-0000")
            ckbLastOrder.Value = True
            ckbLastOrder.Caption = !LastOrder
            txtVendorID.Caption = !VendorID
        End If
End With
.
.
.
'if the user unchecks the checkbox then
'I want to null the field
Set rs1 = db1.OpenRecordset("SELECT * " & _
                                    "FROM [Vendors] " & _
                                    "WHERE VendorID = " & txtVendorID.Value & ";", dbOpenDynaset)


with rs1
    .edit
        !LastOrder = vbNull
    .update
end with


rs1.close
db1.close

Re: Null a date field in a recordset

Hello again Norie. You've bailed me out so many times. By returning the recordset, do you mean to get into the form, or returning it to the database?