Replies

Neat

And you can extend it for more choices

On the sql question, I wonder if it’s his way to make sure the returned recordset has minimal (i.e. zero) records in it ?

On the missing LogId, tried a new database and table in SQL Server, same problem
Tried an Access 2000 table with Autonumber, works fine

Then (with SQL Server connection), tried looking at the LogId after the AddNew, but adding a MoveNext MovePrevious, and now the LogId is there

So quite how/why/what the recordset is doing I’ve no idea

Try this example

I assume you mean he’s got a many-to-many relationship between the Names and Languages table, with an intersection table to handle breaking the many-to-many

Depends what the keys are on the two main tables, but if e.g. in the Language table, it’s the actual name, I’d have thought a query on Name joined to Intersection where the language is like English or French ought to get the records he’s after

I’d go with what pbricker said
create a query, add the table, select the reference field and date field, turn on totals, select group be for reference field, max for the date, run the query
You should then have a list of the maximum date for each unique reference

Charlotte,

to be honest, I’ve no idea why he’s doing that – this is code from another developer, who put together the original DLL. He’s no longer available, so I’ve been given the task of doing the next DLL, so I’m on a crash course to try and learn ADO, XML, ASP and also produce the DLL – stressed, somewhat

I’d seen this and assumed it was needed so the recordset fields would be defined.

I linked the tblLogIn table in an Access 2000 database, and the autonumber works

I’ll have a go at a new SQL Server database, and a new table

You could try first splitting your [oldfield]
then formatting as date
e.g.
Format(left([oldfield],2 & “/” & mid([oldfield],3,2) & “/” mid([oldfield],5,2), “mm/dd/yy”)
– I reversed the day/month as your data looks American format

You could also put a check in that the length of the oldfield is 6 characters

Do you have something small, or nearly invisible that is causing the extra page ?
– I had some horizontal lines which were just wide enough to cause a second ‘blank’ page

Try using the Format function,
e.g. Format([oldfield],”dd/mm/yyyy”) to update the new field which is set to a Date/Time

Yeap I just get a blank dialog !!
– the code in my test forms button

Dim adoCn As New ADODB.Connection
Dim rsLog As New ADODB.Recordset

Dim sql As String
sql = “SELECT * FROM tblLogIn WHERE 1 = 2 ”

adoCn.open msGatewayDbConStr
rsLog.open sql, adoCn, adOpenKeyset, adLockOptimistic, adCmdText

rsLog.AddNew
rsLog!OriginatorAddr = “TestAddr”
rsLog.Update

MsgBox “:” & rsLog!LogId & “:”
MsgBox “:” & rsLog!OriginatorAddr & “:”

rsLog.Close

What is weird is that I change the UDL to point at an Access 2000 database with a copy of the tblLogin database in, and the display of the LogId autonumber works

Not 100% on this, but look at the Startup options, items Menu Bar and Allow Full Menus

Sorry my explanation wasn’t clear, the value I’m after (the rsLog!LogId) isn’t there, so I can’t put it in my audit table
I’ve checked in Watch and the recordset has the values I’ve added in code, e.g. the OriginatorId, but the autonumber LogId (or whatever it’s called in SQL Server speak) isn’t in the recordset, even after the Update
I’m unsure how to refresh the recordset – tried the .Refresh and .Resync and neither did the job

Charlotte,

yes the field increments by one each time I add a new record

The code first does
rsLog.Open sql, adoCn, adOpenKeyset, adLockOptimistic, adCmdText

then
rsLog.AddNew

then some work on the fields
rsLog!OriginatorID = …. value

then finally
rsLog.Update

I can switch to Query Analyzer and the data is in the table, including the new LogId, but the rsLog!LogId is empty – checked with Watch, whereas the other fields have the expected values

Not sure if I’m using client or server cursor – how do I tell that?

I’m sure it’s down to me as two other guys have been using the DLL this code is in, without an problems (or at least no reported problems)

Charlotte,

thanks, interesting thread. Think I’ll have to use something like DAW’s code to check the entered date

I’m still baffled as to why this particular user’s site the date changes to the 01/01/01 style, when on other users and mine it changes to 01/01/2001 – I had him check his regional setting and he said is was UK
Do you know of any internal Access setting / registry / whatever that determines what it’s “guess” on a bad date is?

Thanks

I also found Q208730 which details similar for Access 2000

I’ll have to invest some time in working out how to get the best from the Knowledge Base, there is a LOT in there

Cheers,
Steve C.