Replies

You really need a table that relates the Florist with the zip codes. That would replace the memo field currently trying to be used.

For example: you could add table ZipsForFlorist with fields ZIPCode and MBR. Then you would replace your subform frmZip_info with a form, not a datasheet. On that subform, add a button (you realize this is only one way to approach the problem) that adds that zip for that florist to table ZipsForFlorist (after first checking that that zip is not already there for the florist). Actually, you should add an index to ZipsForFlorist that has fields ZIPCode and MBR and the index is declared unique. After adding the entry to ZipsForFlorist, you will probably have to requery the subform (a new subform) showing the entries in ZipsForFlorist for this florist.

I hope this is not all beyond you.

After I posted the above, I realized that once you have the table ZipsForFlorists, you can build a subform showing it. And you can make the zip a combo box. That combo box can use the same row source as is currently used in the subform frmZip_Info (that is, the zips for a city). The user can then add easily via the combo box new zips. This approach requires less code.

By the way, it would be nice if you updated your profile to show where you are from. I like seeing where posters are located and I suspect others do too.

In looking further, a couple questions you should ask yourself:

1) Why are the zip codes being put in the memo field? Are they not already in the tblActiveFloristZipCode table? If so, why not simply display them as you currently do and forget the memo field?

2) Why is table tblActiveFloristZipCode linked to OwnerID and not MBR? If it should be OwnerID, then why is the user entering MBR? One or the other seems wrong. I’d hazard a guess that a single owner must own more than one florist. This should really be cleaned up.

3) Why do you switch to new entry every time? Why not have a form that positions to the MBR and shows the entries?

WendellB has it right. There are serious design issues. In addition to the things he mentioned, I notice the field MBR is 50 characters in one table and 255 in another. Not good. On the main form, the MBR control accepts the enter key as entry in the field. I suspect that there never is a carriage return in the MBR.

I would suggest you find a colleague to help with the underlying design.

John – thanks for the reference to StrConv. I can’t tell you how many times (more than once for sure) I’ve written code to parse strings, then capitalize the first letter of each word. Nice find!

Reaching back into my memory banks (a risky proposition at best), I seem to remember that you need pass-through queries to run stored procedures. It’s an option when you build a query.

Here is a custom function that gets the max date from a list of any length. Just keep adding arguments. It handles nulls and invalid dates as well.

Code:

Public Function MaxOfDate(ParamArray dates() As Variant) As Date
    Dim MaxDate As Date, i As Integer
    
    MaxDate = 0
    For i = 0 To UBound(dates)
        If (IsDate(dates(i))) Then
            If (Nz(dates(i), 0) > MaxDate) Then MaxDate = dates(i)
        End If
    Next i
    MaxOfDate = MaxDate
    Exit Function
End Function

An example call would be: MaxDate = MaxOfDates (DateField1, DateField 2, DateField3, DateField4, … )

This works best in a form where you have only one record that is being evaluated, but it can work in a query as well, but, as AKW mentioned, it will be slower. In a query, I’d use nested iif statements if this became too slow.

One possibility is to join the “look-up” table and use the linked value to paste instead of the index of the linked value. In that way, you’ll have the displayed value and not have to link to a sharepoint table.

I’m trying to figure out the situation in which this could be a choice. I suppose the 2 million record table is normalized and contains multiple records for each something (person, thing, or whatever), and the 36,000 record table would have only 1 record for each something. Unless you can prove some overwhelming reason to decide otherwise, I’m going to select the normalized version every time.

I would echo Mark’s statement. Always go with normalized and deviate only when forced to do so. You’ll have fewer headaches and more flexibility with that approach.

Christopher: I don’t really have a clue here, but try this. I’ve usually steered away from nulls when working with strings (because I usually want a blank to be treated the same as a null). Since you are working with strings, convert the null to a zero-length string (nz function) and then use the trim function to test the result for zero length. This treats blanks and nulls the same. You can do all this in a single SQL statement. It should not make any difference, but since we’re grasping at straws here… Have you used the immediate window to test for null for these fields (ie. ? isnull(field name) (or is it FieldName = null?)? Do the tests before and after the sql runs.

By the way, the for loop is the better tool for counting type loops. It has a counter built in. You can replace the do with a for and be clearer and simpler (for b = 1 to 3).

Other than that, I’m clueless at the moment.

You have the counter “b” which starts at 1 and goes to 2 – what that has to do with the number of records isn’t clear, but is it possible that with only a single record that the second pass never runs?

The counter loops thru the 3 controls. I think the procedure is invoked once per record.

The ; is good practice in SQL, but not compulsory.
It become more relevant when writing Procedures in SQL Server and other databases.

Interesting. I just eliminated the “;’ in an SQL statement and it worked. I could have sworn that it did not use to work (that could have been anywhere from Access 1.0 onward). Any knowledge about that or was I just imagining it?

Try stepping through the code with a single row being pasted. Look at the values before and after the SQL statement. If it does not behave as you expect, then take that exact SQL into a standalone query and do some testing.

One thing I notice – the semicolon at the end of the SQL is missing. I was not aware of it being optional. Add it and test.

Tip: If you use the QBE Grid/designer, you can double click on the Titlebar of the Field list. That will select all of the fields in the field list and then you can easily drag them all down into the grid and then delete the ones you don’t want.
Bob Oxford

Here I’ve been a developer since version 1.0 and I just learned something new. Thank you.

Also found that you can select the fields in the usual way (using shift and control keys) to select a range or non-consecutive fields and drag them down. Control/A works too! Cool!

Is the code being run for the single case? Have you stepped through the code in debug mode in that case? If not, you should.

One thing come to mind: With a single row paste, when does the record get committed (saved)? And on what event does the code run? And does the code get executed for the last record always? If 773 records are pasted, is the 773rd properly filled in?

Some details might help us all to help you figure out what is occurring.