OpenArgs - works in 2003 - fails in 2007

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Calling process - all variables are dimensioned and have appropriate value


Const DB_PATH As String = "F:\ClientDocs\Programs\SaveFile.mdb"
Path = "F:\ClientDocs\ClientDocuments"

DT = CStr(Now())

' will always have value
OpenArgs = DT & ";" & DocName

' will have value if Client and Year known
If Nz(ClientId, "") <> "" Then
OpenArgs = OpenArgs & ";" & ClientId & ";" & CStr(TheYear)
End If

'On Error GoTo Err_Get_Client_Classify_Path

Set acApp = New Access.Application
With acApp

.OpenCurrentDatabase DB_PATH
.Visible = True

OldObs = .Forms.Count

.DoCmd.OpenForm "frmDocuments_Single", , , , , , OpenArgs

' pause calling routine logic
DoEvents

Do
NewObs = .Forms.Count
If NewObs <> OldObs Then
Else
' form is closed
Exit Do
End If
Loop

Set DataMdb = DBEngine.OpenDatabase(DB_PATH)
sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.DocName = '" & DocName & "' " _
& "AND tblClientDocMaster.DT = '" & DT & "';"

Set rs = DataMdb.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.RecordCount > 0 Then
ClientId = rs("ClientID")
TheYear = rs("Year")
Path = Path & "\" & ClientId & "\" & CStr(TheYear)
End If
rs.Close
Set rs = Nothing
DataMdb.Close
Set DataMdb = Nothing

End With ' I've
since failing on 2007, moved the nxt 3 statements up to just under Loop
'
This has no effect in 2003 - but I haven't tested it in 2007
acApp.Quit ' The failure
occurs well before this- in the loop
Set acApp = Nothing



The Open Form opened above sits in a separate mdb with just the called form.
It is a bound form and there is not any startup.
All variables are dimensioned

The form is bound to the table mentioned before and the record source is an
impossible for its keys.
The bound record is the right shape but empty.


Private Sub Form_Open(Cancel As Integer)

Dim dcy() As String, xx As String

xx = Nz(Me.OpenArgs, "")
If xx = "" Then
Cancel = True
Exit Sub
End If

dcy = Split(xx, ";")

sDateTimeVar = ""
dd = ""
cc = ""
yy = ""

If UBound(dcy) = 3 Then
yy = dcy(3)
cc = Nz(dcy(2), "")
End If

sDateTimeVar = dcy(0)
dd = dcy(1)

If Nz(dd, "") = "" Then
MsgBox "You have NOT Given a Name " & dd & " yet", 0, "Please Try
Again"
Cancel = True
Exit Sub
End If

' the table is 3 way-ed primary keyed ClientID Year and
DocName

If cc = "" Then
' ClientId = "" DocName = dd (passed via OpenArgs) Year =
9999

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & "" & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & 9999 & ";"

' Access 2003 then finds (usually) a NEW record - waiting for its
ClientId to be entered, having its Year set to 9999 and its DocName set to
value of dd.
' The user enters appropriate ClientID, possibly changes Year -
DocName is protected and present - and Saves the record

Else
'All key values are supplied and other fields may be changed

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & cc & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & CInt(yy) & ";"
End If

Me.RecordSource = sSQL

End Sub

This works in Access 2003, but in Access 2007 (the client's version), the
OpenArgs does NOT work and no values are passed across. Also, the Exit
button on the form
which Saves the new/updated record, does NOT click. Another peculiarity -
The form is both Popup and Modal with all Close etc buttons removed.
In 2003 this is a clear blue bar at the top - in 2007, there is a WORKING
x- Close box!

I've spent some time looking through groups about OpenArgs and have noticed
many posts about odd behaviour involving its non-function.
I'm stumped!!!


Michael
 
Hi Michael

I haven't waded through all your code yet, but can you verify that ANY code
is being executed in the database you are opening?

If not, then could it be a problem with trusted locations?
 
I fixed that at the very first, Graham.

Michael
Graham Mandeno said:
Hi Michael

I haven't waded through all your code yet, but can you verify that ANY
code is being executed in the database you are opening?

If not, then could it be a problem with trusted locations?

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Michael said:
Calling process - all variables are dimensioned and have appropriate
value


Const DB_PATH As String = "F:\ClientDocs\Programs\SaveFile.mdb"
Path = "F:\ClientDocs\ClientDocuments"

DT = CStr(Now())

' will always have value
OpenArgs = DT & ";" & DocName

' will have value if Client and Year known
If Nz(ClientId, "") <> "" Then
OpenArgs = OpenArgs & ";" & ClientId & ";" & CStr(TheYear)
End If

'On Error GoTo Err_Get_Client_Classify_Path

Set acApp = New Access.Application
With acApp

.OpenCurrentDatabase DB_PATH
.Visible = True

OldObs = .Forms.Count

.DoCmd.OpenForm "frmDocuments_Single", , , , , , OpenArgs

' pause calling routine logic
DoEvents

Do
NewObs = .Forms.Count
If NewObs <> OldObs Then
Else
' form is closed
Exit Do
End If
Loop

Set DataMdb = DBEngine.OpenDatabase(DB_PATH)
sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.DocName = '" & DocName & "' " _
& "AND tblClientDocMaster.DT = '" & DT & "';"

Set rs = DataMdb.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.RecordCount > 0 Then
ClientId = rs("ClientID")
TheYear = rs("Year")
Path = Path & "\" & ClientId & "\" & CStr(TheYear)
End If
rs.Close
Set rs = Nothing
DataMdb.Close
Set DataMdb = Nothing

End With ' I've
since failing on 2007, moved the nxt 3 statements up to just under Loop

' This has no effect in 2003 - but I haven't tested it in 2007
acApp.Quit ' The
failure occurs well before this- in the loop
Set acApp = Nothing



The Open Form opened above sits in a separate mdb with just the called
form. It is a bound form and there is not any startup.
All variables are dimensioned

The form is bound to the table mentioned before and the record source is
an impossible for its keys.
The bound record is the right shape but empty.


Private Sub Form_Open(Cancel As Integer)

Dim dcy() As String, xx As String

xx = Nz(Me.OpenArgs, "")
If xx = "" Then
Cancel = True
Exit Sub
End If

dcy = Split(xx, ";")

sDateTimeVar = ""
dd = ""
cc = ""
yy = ""

If UBound(dcy) = 3 Then
yy = dcy(3)
cc = Nz(dcy(2), "")
End If

sDateTimeVar = dcy(0)
dd = dcy(1)

If Nz(dd, "") = "" Then
MsgBox "You have NOT Given a Name " & dd & " yet", 0, "Please Try
Again"
Cancel = True
Exit Sub
End If

' the table is 3 way-ed primary keyed ClientID Year and
DocName

If cc = "" Then
' ClientId = "" DocName = dd (passed via OpenArgs) Year =
9999

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & "" & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & 9999 & ";"

' Access 2003 then finds (usually) a NEW record - waiting for its
ClientId to be entered, having its Year set to 9999 and its DocName set
to value of dd.
' The user enters appropriate ClientID, possibly changes Year -
DocName is protected and present - and Saves the record

Else
'All key values are supplied and other fields may be changed

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & cc & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & CInt(yy) & ";"
End If

Me.RecordSource = sSQL

End Sub

This works in Access 2003, but in Access 2007 (the client's version), the
OpenArgs does NOT work and no values are passed across. Also, the Exit
button on the form
which Saves the new/updated record, does NOT click. Another peculiarity -
The form is both Popup and Modal with all Close etc buttons removed.
In 2003 this is a clear blue bar at the top - in 2007, there is a WORKING
x- Close box!

I've spent some time looking through groups about OpenArgs and have
noticed many posts about odd behaviour involving its non-function.
I'm stumped!!!


Michael
Click to expand...
Click to expand...
 
Hi Michael

I'm sorry, I really don't have an answer.

Could you put some diagnostics in the Form_Open procedure to test the values
of OpenArgs and other variables and write them out to a log file or table.
Also, write a record of the execution path of the code through the
procedure.

Another thing to try: add acDialog as the sixth argumnt in your OpenForm.

Why do you need to do this in another session of Access? Why can't you just
open the form in the local database and link the tables if necessary?

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Michael said:
I fixed that at the very first, Graham.

Michael
Graham Mandeno said:
Hi Michael

I haven't waded through all your code yet, but can you verify that ANY
code is being executed in the database you are opening?

If not, then could it be a problem with trusted locations?

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Michael said:
Calling process - all variables are dimensioned and have appropriate
value


Const DB_PATH As String = "F:\ClientDocs\Programs\SaveFile.mdb"
Path = "F:\ClientDocs\ClientDocuments"

DT = CStr(Now())

' will always have value
OpenArgs = DT & ";" & DocName

' will have value if Client and Year known
If Nz(ClientId, "") <> "" Then
OpenArgs = OpenArgs & ";" & ClientId & ";" & CStr(TheYear)
End If

'On Error GoTo Err_Get_Client_Classify_Path

Set acApp = New Access.Application
With acApp

.OpenCurrentDatabase DB_PATH
.Visible = True

OldObs = .Forms.Count

.DoCmd.OpenForm "frmDocuments_Single", , , , , , OpenArgs

' pause calling routine logic
DoEvents

Do
NewObs = .Forms.Count
If NewObs <> OldObs Then
Else
' form is closed
Exit Do
End If
Loop

Set DataMdb = DBEngine.OpenDatabase(DB_PATH)
sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.DocName = '" & DocName & "' " _
& "AND tblClientDocMaster.DT = '" & DT & "';"

Set rs = DataMdb.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.RecordCount > 0 Then
ClientId = rs("ClientID")
TheYear = rs("Year")
Path = Path & "\" & ClientId & "\" & CStr(TheYear)
End If
rs.Close
Set rs = Nothing
DataMdb.Close
Set DataMdb = Nothing

End With ' I've
since failing on 2007, moved the nxt 3 statements up to just under Loop

' This has no effect in 2003 - but I haven't tested it in 2007
acApp.Quit ' The
failure occurs well before this- in the loop
Set acApp = Nothing



The Open Form opened above sits in a separate mdb with just the called
form. It is a bound form and there is not any startup.
All variables are dimensioned

The form is bound to the table mentioned before and the record source is
an impossible for its keys.
The bound record is the right shape but empty.


Private Sub Form_Open(Cancel As Integer)

Dim dcy() As String, xx As String

xx = Nz(Me.OpenArgs, "")
If xx = "" Then
Cancel = True
Exit Sub
End If

dcy = Split(xx, ";")

sDateTimeVar = ""
dd = ""
cc = ""
yy = ""

If UBound(dcy) = 3 Then
yy = dcy(3)
cc = Nz(dcy(2), "")
End If

sDateTimeVar = dcy(0)
dd = dcy(1)

If Nz(dd, "") = "" Then
MsgBox "You have NOT Given a Name " & dd & " yet", 0, "Please Try
Again"
Cancel = True
Exit Sub
End If

' the table is 3 way-ed primary keyed ClientID Year and
DocName

If cc = "" Then
' ClientId = "" DocName = dd (passed via OpenArgs) Year
= 9999

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & "" & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & 9999 & ";"

' Access 2003 then finds (usually) a NEW record - waiting for its
ClientId to be entered, having its Year set to 9999 and its DocName set
to value of dd.
' The user enters appropriate ClientID, possibly changes Year -
DocName is protected and present - and Saves the record

Else
'All key values are supplied and other fields may be changed

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & cc & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & CInt(yy) & ";"
End If

Me.RecordSource = sSQL

End Sub

This works in Access 2003, but in Access 2007 (the client's version),
the OpenArgs does NOT work and no values are passed across. Also, the
Exit button on the form
which Saves the new/updated record, does NOT click. Another
peculiarity - The form is both Popup and Modal with all Close etc
buttons removed.
In 2003 this is a clear blue bar at the top - in 2007, there is a
WORKING x- Close box!

I've spent some time looking through groups about OpenArgs and have
noticed many posts about odd behaviour involving its non-function.
I'm stumped!!!


Michael
Click to expand...
Click to expand...
Click to expand...
 
The syntax is "correct" - it works perfectly in Office 2003.

.DoCmd etc is short for AcApp.DoCmd etc

Looking at the many posts about the operation/non-operation of OpenArgs,
I think that the weakness may be the Select statement which produces the
recordset used by the form


sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & "" & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & 9999 & ";"

which puts an empty string "" into the WHERE clause.

There may be a subtle (unpublished- unrecognised)
difference here between 2003 and 2007

And a fault in one spot often causes many other areas to fail - as I find -
the Exit button
on the called form, ceases to function.

Michael
 
Graham,

The access form is opened from Word or Excel or Outlook or .... another
Office object.

I'm interrupting a replacement SaveAs, to

1. get Client/Year and other data to place into an indexing table (Access)
and
2. use the Client/Year to map into a specific folder whose path contains
those values, to save the document or spreadsheet or email and attachments
coherently.

It works in Office 2003, but fails with an obvious memory fault in 2007.

This Select:

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & "" & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & 9999 & ";"

as applied to

Me.RecordSource = sSQL

might be the problem.

It is a bit peculiar because it sets one of the fields which form the
principal key to "".

Maybe 2003 handles this while 2007 fails.

My one big problem is that I don't have Office 2007 so I can't easily sit
making small changes till I get it right.

I'll have to do this in the client's office - or find the AUD 800+ and buy
it.

Michael



Graham Mandeno said:
Hi Michael

I'm sorry, I really don't have an answer.

Could you put some diagnostics in the Form_Open procedure to test the
values of OpenArgs and other variables and write them out to a log file or
table. Also, write a record of the execution path of the code through the
procedure.

Another thing to try: add acDialog as the sixth argumnt in your OpenForm.

Why do you need to do this in another session of Access? Why can't you
just open the form in the local database and link the tables if necessary?

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Michael said:
I fixed that at the very first, Graham.

Michael
Graham Mandeno said:
Hi Michael

I haven't waded through all your code yet, but can you verify that ANY
code is being executed in the database you are opening?

If not, then could it be a problem with trusted locations?

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Calling process - all variables are dimensioned and have appropriate
value


Const DB_PATH As String = "F:\ClientDocs\Programs\SaveFile.mdb"
Path = "F:\ClientDocs\ClientDocuments"

DT = CStr(Now())

' will always have value
OpenArgs = DT & ";" & DocName

' will have value if Client and Year known
If Nz(ClientId, "") <> "" Then
OpenArgs = OpenArgs & ";" & ClientId & ";" & CStr(TheYear)
End If

'On Error GoTo Err_Get_Client_Classify_Path

Set acApp = New Access.Application
With acApp

.OpenCurrentDatabase DB_PATH
.Visible = True

OldObs = .Forms.Count

.DoCmd.OpenForm "frmDocuments_Single", , , , , , OpenArgs

' pause calling routine logic
DoEvents

Do
NewObs = .Forms.Count
If NewObs <> OldObs Then
Else
' form is closed
Exit Do
End If
Loop

Set DataMdb = DBEngine.OpenDatabase(DB_PATH)
sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.DocName = '" & DocName & "' " _
& "AND tblClientDocMaster.DT = '" & DT & "';"

Set rs = DataMdb.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.RecordCount > 0 Then
ClientId = rs("ClientID")
TheYear = rs("Year")
Path = Path & "\" & ClientId & "\" & CStr(TheYear)
End If
rs.Close
Set rs = Nothing
DataMdb.Close
Set DataMdb = Nothing

End With ' I've
since failing on 2007, moved the nxt 3 statements up to just under Loop

' This has no effect in 2003 - but I haven't tested it in 2007
acApp.Quit ' The
failure occurs well before this- in the loop
Set acApp = Nothing



The Open Form opened above sits in a separate mdb with just the called
form. It is a bound form and there is not any startup.
All variables are dimensioned

The form is bound to the table mentioned before and the record source
is an impossible for its keys.
The bound record is the right shape but empty.


Private Sub Form_Open(Cancel As Integer)

Dim dcy() As String, xx As String

xx = Nz(Me.OpenArgs, "")
If xx = "" Then
Cancel = True
Exit Sub
End If

dcy = Split(xx, ";")

sDateTimeVar = ""
dd = ""
cc = ""
yy = ""

If UBound(dcy) = 3 Then
yy = dcy(3)
cc = Nz(dcy(2), "")
End If

sDateTimeVar = dcy(0)
dd = dcy(1)

If Nz(dd, "") = "" Then
MsgBox "You have NOT Given a Name " & dd & " yet", 0, "Please
Try Again"
Cancel = True
Exit Sub
End If

' the table is 3 way-ed primary keyed ClientID Year and
DocName

If cc = "" Then
' ClientId = "" DocName = dd (passed via OpenArgs) Year
= 9999

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & "" & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & 9999 & ";"

' Access 2003 then finds (usually) a NEW record - waiting for its
ClientId to be entered, having its Year set to 9999 and its DocName set
to value of dd.
' The user enters appropriate ClientID, possibly changes Year -
DocName is protected and present - and Saves the record

Else
'All key values are supplied and other fields may be changed

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & cc & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & CInt(yy) & ";"
End If

Me.RecordSource = sSQL

End Sub

This works in Access 2003, but in Access 2007 (the client's version),
the OpenArgs does NOT work and no values are passed across. Also, the
Exit button on the form
which Saves the new/updated record, does NOT click. Another
peculiarity - The form is both Popup and Modal with all Close etc
buttons removed.
In 2003 this is a clear blue bar at the top - in 2007, there is a
WORKING x- Close box!

I've spent some time looking through groups about OpenArgs and have
noticed many posts about odd behaviour involving its non-function.
I'm stumped!!!


Michael
Click to expand...
Click to expand...
Click to expand...
 
Hi Michael
I'll have to do this in the client's office - or find the AUD 800+ and buy
it.
Click to expand...

You could always download a trial version <g>

Actually, if you DO install Access 2007, I recommend you do it on a
different computer or in a virtual machine, because switching between
versions can be a pain, especially under Vista.

Am I to understand that it fails only when cc is a zero-length string?

I'm a bit confused by this bit:
sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & "" & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & 9999 & ";"

' Access 2003 then finds (usually) a NEW record - waiting for its
ClientId to be entered, having its Year set to 9999 and its DocName
set to value of dd.
Click to expand...

Assuming ClientID is not a text field which allows zero-length, then this
SQL will always return an empty recordset so, assuming AllowAdditions is
True, you will see a new empty record. But how do the DocName and Year
fields get pre-populated with the passed dd value and 9999?

I would do it this way:

If cc = "" Then
Me.DataEntry = True
Me!DocName.DefaultValue = "'" & dd & "'"
Me!Year.DefaultValue = 9999 ' <<< do you really want this?
Me.RecordSource = "tblClientDocMaster"
Else
...

Also, your initial post stated that OpenArgs is failing. Can you verify
this?
Is your code failing to get past If xx = "" Then?

I suggested you put some diagnostics in the Form_Open procedure. Have you
tried this?

Try inserting the following code just before If Nz(dd, "") = "" Then...
(BTW, the Nz here is unnecessary, because dcy(1) cannot possibly be Null)

Dim hFile as Long, i as Integer
hFile = FreeFile
Open CurrentProject.Path & "\SaveFileLog.txt" for Append as #hFile
Print #hFile, "OpenArgs:" ; Tab(15) ; "|" ; Me.OpenArgs ; "|"
For i = LBound(dcy) to UBound(dcy)
Print #hFile, "dcy(" & i & "):" ; Tab(15) ; "|" ; dcy(i) ; "|"
Next i
Print #hFile, "sDateTimeVar:" ; Tab(15) ; "|" ; sDateTimeVar ; "|"
Print #hFile, "dd:" ; Tab(15) ; "|" ; dd ; "|"
Print #hFile, "cc:" ; Tab(15) ; "|" ; cc ; "|"
Print #hFile, "yy:" ; Tab(15) ; "|" ; yy ; "|"
Print #hFile, "=========================="
Close #hFile

Then ask your client to try it in several different situations and email you
the log file. Compare the log with what you get from Access 2003.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Michael said:
Graham,

The access form is opened from Word or Excel or Outlook or .... another
Office object.

I'm interrupting a replacement SaveAs, to

1. get Client/Year and other data to place into an indexing table (Access)
and
2. use the Client/Year to map into a specific folder whose path contains
those values, to save the document or spreadsheet or email and attachments
coherently.

It works in Office 2003, but fails with an obvious memory fault in 2007.

This Select:

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & "" & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & 9999 & ";"

as applied to

Me.RecordSource = sSQL

might be the problem.

It is a bit peculiar because it sets one of the fields which form the
principal key to "".

Maybe 2003 handles this while 2007 fails.

My one big problem is that I don't have Office 2007 so I can't easily sit
making small changes till I get it right.

I'll have to do this in the client's office - or find the AUD 800+ and buy
it.

Michael



Graham Mandeno said:
Hi Michael

I'm sorry, I really don't have an answer.

Could you put some diagnostics in the Form_Open procedure to test the
values of OpenArgs and other variables and write them out to a log file
or table. Also, write a record of the execution path of the code through
the procedure.

Another thing to try: add acDialog as the sixth argumnt in your OpenForm.

Why do you need to do this in another session of Access? Why can't you
just open the form in the local database and link the tables if
necessary?

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Michael said:
I fixed that at the very first, Graham.

Michael
Hi Michael

I haven't waded through all your code yet, but can you verify that ANY
code is being executed in the database you are opening?

If not, then could it be a problem with trusted locations?

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Calling process - all variables are dimensioned and have appropriate
value


Const DB_PATH As String = "F:\ClientDocs\Programs\SaveFile.mdb"
Path = "F:\ClientDocs\ClientDocuments"

DT = CStr(Now())

' will always have value
OpenArgs = DT & ";" & DocName

' will have value if Client and Year known
If Nz(ClientId, "") <> "" Then
OpenArgs = OpenArgs & ";" & ClientId & ";" & CStr(TheYear)
End If

'On Error GoTo Err_Get_Client_Classify_Path

Set acApp = New Access.Application
With acApp

.OpenCurrentDatabase DB_PATH
.Visible = True

OldObs = .Forms.Count

.DoCmd.OpenForm "frmDocuments_Single", , , , , , OpenArgs

' pause calling routine logic
DoEvents

Do
NewObs = .Forms.Count
If NewObs <> OldObs Then
Else
' form is closed
Exit Do
End If
Loop

Set DataMdb = DBEngine.OpenDatabase(DB_PATH)
sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.DocName = '" & DocName & "' " _
& "AND tblClientDocMaster.DT = '" & DT & "';"

Set rs = DataMdb.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.RecordCount > 0 Then
ClientId = rs("ClientID")
TheYear = rs("Year")
Path = Path & "\" & ClientId & "\" & CStr(TheYear)
End If
rs.Close
Set rs = Nothing
DataMdb.Close
Set DataMdb = Nothing

End With ' I've
since failing on 2007, moved the nxt 3 statements up to just under
Loop

' This has no effect in 2003 - but I haven't tested it in 2007
acApp.Quit ' The
failure occurs well before this- in the loop
Set acApp = Nothing



The Open Form opened above sits in a separate mdb with just the called
form. It is a bound form and there is not any startup.
All variables are dimensioned

The form is bound to the table mentioned before and the record source
is an impossible for its keys.
The bound record is the right shape but empty.


Private Sub Form_Open(Cancel As Integer)

Dim dcy() As String, xx As String

xx = Nz(Me.OpenArgs, "")
If xx = "" Then
Cancel = True
Exit Sub
End If

dcy = Split(xx, ";")

sDateTimeVar = ""
dd = ""
cc = ""
yy = ""

If UBound(dcy) = 3 Then
yy = dcy(3)
cc = Nz(dcy(2), "")
End If

sDateTimeVar = dcy(0)
dd = dcy(1)

If Nz(dd, "") = "" Then
MsgBox "You have NOT Given a Name " & dd & " yet", 0, "Please
Try Again"
Cancel = True
Exit Sub
End If

' the table is 3 way-ed primary keyed ClientID Year and
DocName

If cc = "" Then
' ClientId = "" DocName = dd (passed via OpenArgs)
Year = 9999

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & "" & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & 9999 & ";"

' Access 2003 then finds (usually) a NEW record - waiting for
its ClientId to be entered, having its Year set to 9999 and its
DocName set to value of dd.
' The user enters appropriate ClientID, possibly changes Year -
DocName is protected and present - and Saves the record

Else
'All key values are supplied and other fields may be changed

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & cc & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & CInt(yy) & ";"
End If

Me.RecordSource = sSQL

End Sub

This works in Access 2003, but in Access 2007 (the client's version),
the OpenArgs does NOT work and no values are passed across. Also, the
Exit button on the form
which Saves the new/updated record, does NOT click. Another
peculiarity - The form is both Popup and Modal with all Close etc
buttons removed.
In 2003 this is a clear blue bar at the top - in 2007, there is a
WORKING x- Close box!

I've spent some time looking through groups about OpenArgs and have
noticed many posts about odd behaviour involving its non-function.
I'm stumped!!!


Michael
Click to expand...
Click to expand...
Click to expand...
 
I appreciate your efforts Graham,

I've examined the access oject and form that I'm calling fron various
other Office modules.

I'm opening Access, executing the form (with OpenArgs) in the same way,
but I've looked at the form a bit more closely.

1. The form is NOT bound to any recordset
2. I parse the openargs and split out the varaibles - I've checked this
rigorously
3. In FormLoad, I assign the 2 known, passed over values to their respective
controls, which are not enabled
4. The user selects values for the Client Id, Year and various fields.
5. On pressing Exit, a series of steps take place.
a. An empty record is started - A recordset opened on the desired table
with a single and impossible WHERE
This results in a a totally empty/null ? record set instance.
b. Using Addnew (DAO), I fill in the recordset columns from the form
controls, finishing with Update
This writes the now filled-in record away. I have used this
technique many times before as had the
person who showed it to me.
c. Everything is neatly closed and "nothing"ed.
6. The calling loop senses the form closure and closes/quits the Access
object.

Back in Word/Excel etc, the vba opens up the table (again DAO, but not
within any Access object) and selects
the freshly written record using the unique "Date/Time" I passed over as its
search value,
finds the record, extracts the newly entered Client and Year values and uses
this to create/locate the correct folder
and save the doc/spread......

It works smoothly in 2003. Will it work in 2007? The Access form's code is
less ambiguous.

I'll try it next week.

Thanks
Michael



Graham Mandeno said:
Hi Michael
I'll have to do this in the client's office - or find the AUD 800+ and
buy it.
Click to expand...

You could always download a trial version <g>

Actually, if you DO install Access 2007, I recommend you do it on a
different computer or in a virtual machine, because switching between
versions can be a pain, especially under Vista.

Am I to understand that it fails only when cc is a zero-length string?

I'm a bit confused by this bit:
sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & "" & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & 9999 & ";"

' Access 2003 then finds (usually) a NEW record - waiting for its
ClientId to be entered, having its Year set to 9999 and its DocName
set to value of dd.
Click to expand...

Assuming ClientID is not a text field which allows zero-length, then this
SQL will always return an empty recordset so, assuming AllowAdditions is
True, you will see a new empty record. But how do the DocName and Year
fields get pre-populated with the passed dd value and 9999?

I would do it this way:

If cc = "" Then
Me.DataEntry = True
Me!DocName.DefaultValue = "'" & dd & "'"
Me!Year.DefaultValue = 9999 ' <<< do you really want this?
Me.RecordSource = "tblClientDocMaster"
Else
...

Also, your initial post stated that OpenArgs is failing. Can you verify
this?
Is your code failing to get past If xx = "" Then?

I suggested you put some diagnostics in the Form_Open procedure. Have you
tried this?

Try inserting the following code just before If Nz(dd, "") = "" Then...
(BTW, the Nz here is unnecessary, because dcy(1) cannot possibly be Null)

Dim hFile as Long, i as Integer
hFile = FreeFile
Open CurrentProject.Path & "\SaveFileLog.txt" for Append as #hFile
Print #hFile, "OpenArgs:" ; Tab(15) ; "|" ; Me.OpenArgs ; "|"
For i = LBound(dcy) to UBound(dcy)
Print #hFile, "dcy(" & i & "):" ; Tab(15) ; "|" ; dcy(i) ; "|"
Next i
Print #hFile, "sDateTimeVar:" ; Tab(15) ; "|" ; sDateTimeVar ; "|"
Print #hFile, "dd:" ; Tab(15) ; "|" ; dd ; "|"
Print #hFile, "cc:" ; Tab(15) ; "|" ; cc ; "|"
Print #hFile, "yy:" ; Tab(15) ; "|" ; yy ; "|"
Print #hFile, "=========================="
Close #hFile

Then ask your client to try it in several different situations and email
you the log file. Compare the log with what you get from Access 2003.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Michael said:
Graham,

The access form is opened from Word or Excel or Outlook or .... another
Office object.

I'm interrupting a replacement SaveAs, to

1. get Client/Year and other data to place into an indexing table
(Access) and
2. use the Client/Year to map into a specific folder whose path contains
those values, to save the document or spreadsheet or email and
attachments
coherently.

It works in Office 2003, but fails with an obvious memory fault in 2007.

This Select:

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & "" & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & 9999 & ";"

as applied to

Me.RecordSource = sSQL

might be the problem.

It is a bit peculiar because it sets one of the fields which form the
principal key to "".

Maybe 2003 handles this while 2007 fails.

My one big problem is that I don't have Office 2007 so I can't easily sit
making small changes till I get it right.

I'll have to do this in the client's office - or find the AUD 800+ and
buy it.

Michael



Graham Mandeno said:
Hi Michael

I'm sorry, I really don't have an answer.

Could you put some diagnostics in the Form_Open procedure to test the
values of OpenArgs and other variables and write them out to a log file
or table. Also, write a record of the execution path of the code through
the procedure.

Another thing to try: add acDialog as the sixth argumnt in your
OpenForm.

Why do you need to do this in another session of Access? Why can't you
just open the form in the local database and link the tables if
necessary?

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I fixed that at the very first, Graham.

Michael
Hi Michael

I haven't waded through all your code yet, but can you verify that ANY
code is being executed in the database you are opening?

If not, then could it be a problem with trusted locations?

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Calling process - all variables are dimensioned and have appropriate
value


Const DB_PATH As String = "F:\ClientDocs\Programs\SaveFile.mdb"
Path = "F:\ClientDocs\ClientDocuments"

DT = CStr(Now())

' will always have value
OpenArgs = DT & ";" & DocName

' will have value if Client and Year known
If Nz(ClientId, "") <> "" Then
OpenArgs = OpenArgs & ";" & ClientId & ";" & CStr(TheYear)
End If

'On Error GoTo Err_Get_Client_Classify_Path

Set acApp = New Access.Application
With acApp

.OpenCurrentDatabase DB_PATH
.Visible = True

OldObs = .Forms.Count

.DoCmd.OpenForm "frmDocuments_Single", , , , , , OpenArgs

' pause calling routine logic
DoEvents

Do
NewObs = .Forms.Count
If NewObs <> OldObs Then
Else
' form is closed
Exit Do
End If
Loop

Set DataMdb = DBEngine.OpenDatabase(DB_PATH)
sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster "
_
& "WHERE tblClientDocMaster.DocName = '" & DocName & "' " _
& "AND tblClientDocMaster.DT = '" & DT & "';"

Set rs = DataMdb.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.RecordCount > 0 Then
ClientId = rs("ClientID")
TheYear = rs("Year")
Path = Path & "\" & ClientId & "\" & CStr(TheYear)
End If
rs.Close
Set rs = Nothing
DataMdb.Close
Set DataMdb = Nothing

End With '
I've since failing on 2007, moved the nxt 3 statements up to just
under Loop

' This has no effect in 2003 - but I haven't tested it in 2007
acApp.Quit ' The
failure occurs well before this- in the loop
Set acApp = Nothing



The Open Form opened above sits in a separate mdb with just the
called form. It is a bound form and there is not any startup.
All variables are dimensioned

The form is bound to the table mentioned before and the record source
is an impossible for its keys.
The bound record is the right shape but empty.


Private Sub Form_Open(Cancel As Integer)

Dim dcy() As String, xx As String

xx = Nz(Me.OpenArgs, "")
If xx = "" Then
Cancel = True
Exit Sub
End If

dcy = Split(xx, ";")

sDateTimeVar = ""
dd = ""
cc = ""
yy = ""

If UBound(dcy) = 3 Then
yy = dcy(3)
cc = Nz(dcy(2), "")
End If

sDateTimeVar = dcy(0)
dd = dcy(1)

If Nz(dd, "") = "" Then
MsgBox "You have NOT Given a Name " & dd & " yet", 0, "Please
Try Again"
Cancel = True
Exit Sub
End If

' the table is 3 way-ed primary keyed ClientID Year and
DocName

If cc = "" Then
' ClientId = "" DocName = dd (passed via OpenArgs) Year =
9999

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster "
_
& "WHERE tblClientDocMaster.ClientID = '" & "" & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & 9999 & ";"

' Access 2003 then finds (usually) a NEW record - waiting for
its ClientId to be entered, having its Year set to 9999 and its
DocName set to value of dd.
' The user enters appropriate ClientID, possibly changes Year -
DocName is protected and present - and Saves the record

Else
'All key values are supplied and other fields may be changed

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster "
_
& "WHERE tblClientDocMaster.ClientID = '" & cc & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & CInt(yy) & ";"
End If

Me.RecordSource = sSQL

End Sub

This works in Access 2003, but in Access 2007 (the client's version),
the OpenArgs does NOT work and no values are passed across. Also, the
Exit button on the form
which Saves the new/updated record, does NOT click. Another
peculiarity - The form is both Popup and Modal with all Close etc
buttons removed.
In 2003 this is a clear blue bar at the top - in 2007, there is a
WORKING x- Close box!

I've spent some time looking through groups about OpenArgs and have
noticed many posts about odd behaviour involving its non-function.
I'm stumped!!!


Michael
Click to expand...
Click to expand...
Click to expand...
 
I've dim(ed) OpenArgs as string and tidied up other potential 2007, but not
2003 problems.
- see end of other strand.

Thanks, for your help
Michael
 
Hi Michael
1. The form is NOT bound to any recordset
Click to expand...

Yes it is! Perhaps not initially, but it will be after this line of code is
executed:
Me.RecordSource = sSQL

2. I parse the openargs and split out the varaibles - I've checked this
rigorously
Click to expand...

Yes, I'm sure you've checked it rigorously in your development environment
(Access 2003), but have you checked it in your client's environment? This
is why I suggest you generate a log file and have the results returned to
you.
5. On pressing Exit, a series of steps take place.
a. An empty record is started - A recordset opened on the desired table
with a single and impossible WHERE
This results in a a totally empty/null ? record set instance.
Click to expand...

In may last post I suggested that DataEntry=True and an unfiltered
RecordSource is a better way of doing this than an "impossible WHERE". I
don't think this is the cause of your problem though.
b. Using Addnew (DAO), I fill in the recordset columns from the form
controls, finishing with Update
Click to expand...

You have a bound Recordset and yet you are using AddNew on another
Recordset? There may be some conflict here. I would like to see your code
for the Exit_Click event.
This writes the now filled-in record away. I have used this
technique many times before as had the
person who showed it to me.
Click to expand...

Clearly this is a method which you have found to work. The problem is that
it does not work in a certain environment. I strongly urge you to create a
diagnostic log file to find out what is going on in that environment.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Michael said:
I appreciate your efforts Graham,

I've examined the access oject and form that I'm calling fron various
other Office modules.

I'm opening Access, executing the form (with OpenArgs) in the same way,
but I've looked at the form a bit more closely.

1. The form is NOT bound to any recordset
2. I parse the openargs and split out the varaibles - I've checked this
rigorously
3. In FormLoad, I assign the 2 known, passed over values to their
respective
controls, which are not enabled
4. The user selects values for the Client Id, Year and various fields.
5. On pressing Exit, a series of steps take place.
a. An empty record is started - A recordset opened on the desired table
with a single and impossible WHERE
This results in a a totally empty/null ? record set instance.
b. Using Addnew (DAO), I fill in the recordset columns from the form
controls, finishing with Update
This writes the now filled-in record away. I have used this
technique many times before as had the
person who showed it to me.
c. Everything is neatly closed and "nothing"ed.
6. The calling loop senses the form closure and closes/quits the Access
object.

Back in Word/Excel etc, the vba opens up the table (again DAO, but not
within any Access object) and selects
the freshly written record using the unique "Date/Time" I passed over as
its search value,
finds the record, extracts the newly entered Client and Year values and
uses this to create/locate the correct folder
and save the doc/spread......

It works smoothly in 2003. Will it work in 2007? The Access form's code
is less ambiguous.

I'll try it next week.

Thanks
Michael



Graham Mandeno said:
Hi Michael
I'll have to do this in the client's office - or find the AUD 800+ and
buy it.
Click to expand...

You could always download a trial version <g>

Actually, if you DO install Access 2007, I recommend you do it on a
different computer or in a virtual machine, because switching between
versions can be a pain, especially under Vista.

Am I to understand that it fails only when cc is a zero-length string?

I'm a bit confused by this bit:
sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & "" & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & 9999 & ";"

' Access 2003 then finds (usually) a NEW record - waiting for its
ClientId to be entered, having its Year set to 9999 and its DocName
set to value of dd.
Click to expand...

Assuming ClientID is not a text field which allows zero-length, then this
SQL will always return an empty recordset so, assuming AllowAdditions is
True, you will see a new empty record. But how do the DocName and Year
fields get pre-populated with the passed dd value and 9999?

I would do it this way:

If cc = "" Then
Me.DataEntry = True
Me!DocName.DefaultValue = "'" & dd & "'"
Me!Year.DefaultValue = 9999 ' <<< do you really want this?
Me.RecordSource = "tblClientDocMaster"
Else
...

Also, your initial post stated that OpenArgs is failing. Can you verify
this?
Is your code failing to get past If xx = "" Then?

I suggested you put some diagnostics in the Form_Open procedure. Have
you tried this?

Try inserting the following code just before If Nz(dd, "") = "" Then...
(BTW, the Nz here is unnecessary, because dcy(1) cannot possibly be Null)

Dim hFile as Long, i as Integer
hFile = FreeFile
Open CurrentProject.Path & "\SaveFileLog.txt" for Append as #hFile
Print #hFile, "OpenArgs:" ; Tab(15) ; "|" ; Me.OpenArgs ; "|"
For i = LBound(dcy) to UBound(dcy)
Print #hFile, "dcy(" & i & "):" ; Tab(15) ; "|" ; dcy(i) ; "|"
Next i
Print #hFile, "sDateTimeVar:" ; Tab(15) ; "|" ; sDateTimeVar ; "|"
Print #hFile, "dd:" ; Tab(15) ; "|" ; dd ; "|"
Print #hFile, "cc:" ; Tab(15) ; "|" ; cc ; "|"
Print #hFile, "yy:" ; Tab(15) ; "|" ; yy ; "|"
Print #hFile, "=========================="
Close #hFile

Then ask your client to try it in several different situations and email
you the log file. Compare the log with what you get from Access 2003.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Michael said:
Graham,

The access form is opened from Word or Excel or Outlook or .... another
Office object.

I'm interrupting a replacement SaveAs, to

1. get Client/Year and other data to place into an indexing table
(Access) and
2. use the Client/Year to map into a specific folder whose path contains
those values, to save the document or spreadsheet or email and
attachments
coherently.

It works in Office 2003, but fails with an obvious memory fault in 2007.

This Select:

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster " _
& "WHERE tblClientDocMaster.ClientID = '" & "" & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & 9999 & ";"

as applied to

Me.RecordSource = sSQL

might be the problem.

It is a bit peculiar because it sets one of the fields which form the
principal key to "".

Maybe 2003 handles this while 2007 fails.

My one big problem is that I don't have Office 2007 so I can't easily
sit making small changes till I get it right.

I'll have to do this in the client's office - or find the AUD 800+ and
buy it.

Michael



Hi Michael

I'm sorry, I really don't have an answer.

Could you put some diagnostics in the Form_Open procedure to test the
values of OpenArgs and other variables and write them out to a log file
or table. Also, write a record of the execution path of the code
through the procedure.

Another thing to try: add acDialog as the sixth argumnt in your
OpenForm.

Why do you need to do this in another session of Access? Why can't you
just open the form in the local database and link the tables if
necessary?

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I fixed that at the very first, Graham.

Michael
Hi Michael

I haven't waded through all your code yet, but can you verify that
ANY code is being executed in the database you are opening?

If not, then could it be a problem with trusted locations?

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Calling process - all variables are dimensioned and have appropriate
value


Const DB_PATH As String = "F:\ClientDocs\Programs\SaveFile.mdb"
Path = "F:\ClientDocs\ClientDocuments"

DT = CStr(Now())

' will always have value
OpenArgs = DT & ";" & DocName

' will have value if Client and Year known
If Nz(ClientId, "") <> "" Then
OpenArgs = OpenArgs & ";" & ClientId & ";" & CStr(TheYear)
End If

'On Error GoTo Err_Get_Client_Classify_Path

Set acApp = New Access.Application
With acApp

.OpenCurrentDatabase DB_PATH
.Visible = True

OldObs = .Forms.Count

.DoCmd.OpenForm "frmDocuments_Single", , , , , , OpenArgs

' pause calling routine logic
DoEvents

Do
NewObs = .Forms.Count
If NewObs <> OldObs Then
Else
' form is closed
Exit Do
End If
Loop

Set DataMdb = DBEngine.OpenDatabase(DB_PATH)
sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster "
_
& "WHERE tblClientDocMaster.DocName = '" & DocName & "' " _
& "AND tblClientDocMaster.DT = '" & DT & "';"

Set rs = DataMdb.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.RecordCount > 0 Then
ClientId = rs("ClientID")
TheYear = rs("Year")
Path = Path & "\" & ClientId & "\" & CStr(TheYear)
End If
rs.Close
Set rs = Nothing
DataMdb.Close
Set DataMdb = Nothing

End With '
I've since failing on 2007, moved the nxt 3 statements up to just
under Loop

' This has no effect in 2003 - but I haven't tested it in 2007
acApp.Quit ' The
failure occurs well before this- in the loop
Set acApp = Nothing



The Open Form opened above sits in a separate mdb with just the
called form. It is a bound form and there is not any startup.
All variables are dimensioned

The form is bound to the table mentioned before and the record
source is an impossible for its keys.
The bound record is the right shape but empty.


Private Sub Form_Open(Cancel As Integer)

Dim dcy() As String, xx As String

xx = Nz(Me.OpenArgs, "")
If xx = "" Then
Cancel = True
Exit Sub
End If

dcy = Split(xx, ";")

sDateTimeVar = ""
dd = ""
cc = ""
yy = ""

If UBound(dcy) = 3 Then
yy = dcy(3)
cc = Nz(dcy(2), "")
End If

sDateTimeVar = dcy(0)
dd = dcy(1)

If Nz(dd, "") = "" Then
MsgBox "You have NOT Given a Name " & dd & " yet", 0, "Please
Try Again"
Cancel = True
Exit Sub
End If

' the table is 3 way-ed primary keyed ClientID Year and
DocName

If cc = "" Then
' ClientId = "" DocName = dd (passed via OpenArgs) Year
= 9999

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster "
_
& "WHERE tblClientDocMaster.ClientID = '" & "" & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & 9999 & ";"

' Access 2003 then finds (usually) a NEW record - waiting for
its ClientId to be entered, having its Year set to 9999 and its
DocName set to value of dd.
' The user enters appropriate ClientID, possibly changes Year -
DocName is protected and present - and Saves the record

Else
'All key values are supplied and other fields may be changed

sSQL = "SELECT tblClientDocMaster.* FROM tblClientDocMaster "
_
& "WHERE tblClientDocMaster.ClientID = '" & cc & "' AND
tblClientDocMaster.DocName = '" & dd & "' " _
& "AND tblClientDocMaster.Year = " & CInt(yy) & ";"
End If

Me.RecordSource = sSQL

End Sub

This works in Access 2003, but in Access 2007 (the client's
version), the OpenArgs does NOT work and no values are passed
across. Also, the Exit button on the form
which Saves the new/updated record, does NOT click. Another
peculiarity - The form is both Popup and Modal with all Close etc
buttons removed.
In 2003 this is a clear blue bar at the top - in 2007, there is a
WORKING x- Close box!

I've spent some time looking through groups about OpenArgs and have
noticed many posts about odd behaviour involving its non-function.
I'm stumped!!!


Michael
Click to expand...
Click to expand...
Click to expand...
 
Back
Top