Replies

SQL Server expects dates to be between single quotes, a la ‘1/1/2003’

I’m confused. How does FP clobber the code — by inserting the code block in your initial post where one didn’t exist before, or by altering code you’d already written?

Given the design you’re moving to, I think you want to loop through each control in the form’s Controls collection, see if it’s a toggle button (acToggleButton). If it is, see if it has been depressed, i.e. its value is True. For each depressed button, you’ll build up the WHERE clause for your query. Simple, right?

I assume you’re familiar with building up a SQL statement in code given your current use of the multi-select list box. Check these two posts for discussions on how to loop through controls: 1 and 2.

Normally I’m not one to critique another’s design choice, but you may want to reconsider the 50 toggle buttons. I simply can’t conceive of a layout that would make a form with 50 toggle buttons visually appealing. Good luck!

Oof! I don’t envy the problem, but I’m pretty curious to see how you handled it.

There sure is. The magic combination of methods, properties and events are: the AfterUpdate event of your multi-select enabled list box and the ListBox.ItemsSelected.Count property. Whether you click to choose, or click to unchoose a selection in a list box, you’ll fire the AfterUpdate event for the control. You can examine the number of selected items, and do something with it. So, to come close to your scenario, imagine your list box is named lstTest, and the text box is named txtCount. The code would be something like:

Private Sub lstTest_AfterUpdate()
    Dim intNumChoices As Integer
    
    intNumChoices = Me.lstTest.ItemsSelected.Count
    
    Me.txtCount.Value = intNumChoices

End Sub

Good luck!

Peter:

If you’re looking to wade into ASP.NET, I commend Web Matrix to you. It’s freeware, but really rather functional. Not to mention the pre-made components they have with handy “TO DO” comments in the code and an online tutorial. Change the appropriate variables, and you’re in business. Good luck!

In addition to the previous posts, make sure you’ve renamed the control on the form for latedate to be something else, say txtLateDate, or even if you’ve constructed the Nz() function properly and your substitute value is of the appropriate data type, you’ll have set up a circular reference and you’ll see #Name or #Error (I forget which) displayed. Good luck!

Thanks, guys, for posting responses. As it’s an app for our IT group, it doesn’t get a lot of attention so I’m only getting back to it today. Shoemaker’s kids, y’know!

I checked the underlying query, and confirmed that it is returning the full entry in the field, so that’s ruled out. I changed the data type from nvarchar to varchar, but no dice there either. I changed the AfterUpdate event to use DLookup with the criteria from the list box selection, and VIOLA! All this leads me to believe that the maximum number of characters you can use in a single column in a list box, and probably a combo box as well, is 255 characters, though I can’t find definitive proof in the documentation.

DLookup’s performance seems to be just fine right now; it’s based on a linked SQL 2000 table with only a couple thousand records. I fear that performance will degrade as the record set grows, so I’ll probably create a stored proc, pass it the NoteID from the list box, and return the value into the text box for display.

Thanks again for your help! Cheers!

Good! I’m glad your testing for recovery is working out OK — here’s hoping you won’t have to use it!!

What are the advantages of using a backup agent like Open File Manager? Well, I guess you’d save on storage space and eliminate a level of complexity. The agent would back the database(s) up directly to its media, say a tape, rather than SQL backing up the database(s) to a network location and the agent then backing up the contents of that network location to tape.

Um…any one else want to chime in here? My implementation of SQL 2000 is not a high volume transaction implementation, but more of a data warehousing implementation. We use ARCserve to back up relevant databases nightly, which is overkill, honestly, and I use right-click quick backups when I need to which ain’t all that often.

It should, assuming there haven’t been any changes to the data since your backup, though I wouldn’t recommend restoring the system databases without a really, really, really good reason! That is, practice backing them up, but only restore your test database.

The scenario I had in my mind during the previous post was this: You back up the relevant database, then perform the application upgrade. Nuts, the upgrade didn’t go as promised by the vendor (a shocking and wholly implausible scenario, I know! ), and so you roll back the upgrade and restore the backup of your data database. If the upgrade made changes to the system databases, you might still be in trouble and those system database backups would come in handy. But complete lack of functionality with the threat of termination hanging over you is the only condition under which I’d consider restoring system databases.

By the way, or BTW, in a car tuning forum that I cruise from time to time, they not only have forum acronyms to contend with, but car and after-market manufacturer and tuning and part and on and on acronyms as well. It’s so confusing, somebody had to compose an Acronym FAQ! Silliness reigns!

Sorry, sometimes acronyms get the better of me! That would be In My Humble Opinion.

SQL Server does expose a pretty easy way to do this. Open up SQL Server Enterprise Manager, and drill into your server until you find the database you want to back up. Right click on the database, choose All Tasks -> Backup Database, and follow the wizard down the yellow brick path! I

I have to admit that I’m a little confused about what you’re up to, but Rory has you pointed in the right direction, IMHO. If you don’t have backup software that can back up SQL Server db’s directly, then backing up each database to a file with that file in a network directory that your backup software can get to should give you the level of protection you need. If it’s a database that has a high number of transactions, you may also consider backing up your transaction logs to a network location your backup software can access. Good luck!

Yes, you can directly manipulate them, but no, you shouldn’t. Nor, as the official line goes, should you make great use of them as that would be unsupported by MS and the structure of those system tables is not guaranteed from version to version. In fact, pretend they’re a solar eclispse and never look directly at them! If you’re feeling spanky, you can view system tables in the database container by clicking Tools -> Options, choosing the View tab and checking the Hidden Objects and System Objects check boxes.

In a nut shell, they contain configuration information for your database. For example, if you create an Import Specification naming all of the fields, setting their data types, their start positions and lengths, etc., all of the information can be found in MSysIMEXSpecs and MSysIMEXColumns tables.

So…

Dim dblTest1 as Double
Dim dblTest2 as Double
Dim dblTest3 as Double
dblTest1 = 2147483647
dblTest2 = 2147483648#
dblTest3 = 60614907704#
Debug.Print dblTest3 * 2

..even though the IDE places the # at the end of the values, the actual value will be used in calculations. Interesting. Good catch, Pat!

Phase of the moon — check the Help topic titled, “Silly things that happen during a diurnal moon.”

When you’re keying in the value, are you keying it directly into a table, into a form field, the VBA IDE? If a table, form or query, what is the data type of the underlying field? If you’re keying it into a form field, is there an event of any sort associated with the control that might cause the pound/sharp sign to be appended? Are you keying in just the numeric value in your original post, or the whole text string, “tempSAP = 60614907704”? A little more info, please.