Linked SQL tables/popup form problem

[EManning]

I have an A2003 database linked to SQL Server 2005. My problem is
with a popup form with a filtered table as a recordsource. I set the
filter with an SQL statement like this:

SELECT * FROM tblMedicalTraits WHERE SystemID = " & CLng(Me.OpenArgs)
Me.RecordSource = strSQL

This opens the popup form just fine when there are records that meet
the filter. If there are no records, then the popup displays 2
"blank" records. What I expect to see is one blank record. It's
almost like there is already a record out there but it's blank. (This
could be better explained if I could attach a screen-shot of what I'm
seeing.) If I try to close the popup without entering anything, I get
an error message that a null cannot be inserted into a field in
tblMedicalTraits. It's referring to the SystemID column in
tblMedicalTraits.

This does not happen if there are records already in the table that
meet the filter criteria. I can enter/edit/delete records just fine.
It happens only when there are no records.

tblMedicalTraits has an IDENTITY field in it so that it will link
correctly to the mdb. It also has a TIMESTAMP field in it to avoid
any write conflicts that these types of databases sometimes get. I
tried removing both thinking that one of them was causing this
problem. But if I remove the IDENTITY field, I just get a popup form
with no fields on it at all, just the outline of the form

Has anybody had this problem? If so, what did you do to correct it?
Thanks for any help or advice.

[Rich P]

Are you running the sql statement in the popup form's load event? If
you are (or if you aren't you should run the statement in the form load
event) you should add an "If/Then" statement

Private Sub frmPopup_Load()
If Not IsNull(Me.OpenArgs) or Me.OpenArgs <"" then
...
End If
End Sub

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[EManning]

On Jun 2, 1:40*pm, Rich P <[e-mail]:

Are you running the sql statement in the popup form's load event? *If
you are (or if you aren't you should run the statement in the form load
event) you should add an "If/Then" statement

Private Sub frmPopup_Load()
* *If Not IsNull(Me.OpenArgs) or Me.OpenArgs <"" then
* * * ...
* *End If
End Sub

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Thanks for your reply. Yes, the statement is in the Form_Load event.
I check for null OpenArgs in the load event before the SQL statement
is executed. So unless I'm missing something, what you suggested is
good programming practice but it doesn't help my situation.

[Rich P]

I overlooked the part where you are linking to a sql server 2005 table.
That changes things a little bit. For that I would use ADO. Keep using
OpenArgs, and

If OpenArgs is not null and <"" then pull the records using ADO.

So - instead of having tblMedicalTraits be a linked table - make it a
local table. You populate it as needed with ADO. When you call the
Popup form - first clear the table

Private Sub Form_Load()
DoCmd.RunSql "Delete * From tblMedicalTraits"
If Not IsNull(Me.OpenArgs) And Me.OpenArgs <"" Then
Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
Dim RS1 As DAO.Recordset, i As Integer
Set RS1 = CurrentDB.OpenRecordset("tblMedicalTraits")
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourSvr;Database=yourDB;Trusted_Connection= Yes"
cmd.CommandText = "Select * From tblX Where someArg = '" & me.OpenArgs
& "'"
Set RS = cmd.Execute
While Not RS.EOF
RS1.AddNew
For i = 0 to RS.Fields.Count - 1
RS1(i) = RS(i)
Next
RS1.Update
RS.MoveNext
Loop
End If
...
End Sub

Just Make sure you make a reference to Microsoft ActiveX DataObjects 2.x
Library in Tools/References first.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[EManning]

On Jun 2, 2:42*pm, Rich P <[e-mail]:

I overlooked the part where you are linking to a sql server 2005 table.
That changes things a little bit. *For that I would use ADO. *Keep using
OpenArgs, and

If OpenArgs is not null and <"" then pull the records using ADO. *

So - instead of having tblMedicalTraits be a linked table - make it a
local table. *You populate it as needed with ADO. *When you call the
Popup form - first clear the table

Private Sub Form_Load()
DoCmd.RunSql "Delete * From tblMedicalTraits"
If Not IsNull(Me.OpenArgs) And Me.OpenArgs <"" Then
* Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
* Dim RS1 As DAO.Recordset, i As Integer
* Set RS1 = CurrentDB.OpenRecordset("tblMedicalTraits")
* cmd.ActiveConnection *= "Provider=SQLOLEDB; Data
Source=yourSvr;Database=yourDB;Trusted_Connection= Yes"
* cmd.CommandText = "Select * From tblX Where someArg = '" & me.OpenArgs
& "'"
* Set RS = cmd.Execute
* While Not RS.EOF
* * RS1.AddNew
* * For i = 0 to RS.Fields.Count - 1
* * * RS1(i) = RS(i)
* * Next
* * RS1.Update
* * RS.MoveNext
* Loop
End If
..
End Sub

Just Make sure you make a reference to Microsoft ActiveX DataObjects 2.x
Library in Tools/References first.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

This table is used by multiple users. So everytime the popup form is
opened, I would have to recreate the table locally, fill it with data,
then append it to the table linked to SQL Server?

[lyle fairfield]

I can't duplicate this problem. I show one record only.

Do you have Navigation Buttons Showing? How many records does it show?

Perhaps there is a blank record in tblMedicaltraits?

On Jun 2, 1:09*pm, EManning <[e-mail]:

I have an A2003 database linked to SQL Server 2005. *My problem is
with a popup form with a filtered table as a recordsource. *I set the
filter with an SQL statement like this:

SELECT * FROM tblMedicalTraits WHERE SystemID = " & CLng(Me.OpenArgs)
Me.RecordSource = strSQL

This opens the popup form just fine when there are records that meet
the filter. *If there are no records, then the popup displays 2
"blank" records. *What I expect to see is one blank record. *It's
almost like there is already a record out there but it's blank. *(This
could be better explained if I could attach a screen-shot of what I'm
seeing.) *If I try to close the popup without entering anything, I get
an error message that a null cannot be inserted into a field in
tblMedicalTraits. *It's referring to the SystemID column in
tblMedicalTraits.

This does not happen if there are records already in the table that
meet the filter criteria. *I can enter/edit/delete records just fine.
It happens only when there are no records.

tblMedicalTraits has an IDENTITY field in it so that it will link
correctly to the mdb. *It also has a TIMESTAMP field in it to avoid
any write conflicts that these types of databases sometimes get. *I
tried removing both thinking that one of them was causing this
problem. *But if I remove the IDENTITY field, I just get a popup form
with no fields on it at all, just the outline of the form

Has anybody had this problem? *If so, what did you do to correct it?
Thanks for any help or advice.

[Rich P]

If all the users are using the same Front End -- you need to change
that. Each user should have an individual copy of the front end. Of
course, now you introduce deployment/distribution issues. This has been
an on-going/age old problem in the Access community -- trying to use
Access in a Multi-User/Corporate environment. Thus came the emergence
of the .Net environment which specifically addresses and solves these
issues.

When you are talking Microsoft Server DB's and Multi-User environment --
you should be thinking "I need something that was specifically designed
for this kind of environment - I realize that Access is a file based
RDBMS and thus not the most ideal solution for a Server DB/Multi-user
based project".

In the meantime, I would go with the Individual copies of the Front end
for each user. This is the alternative to the main solution of stepping
up to the .Net environment for this kind of project.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[EManning]

On Jun 2, 6:25*pm, Rich P <[e-mail]:

If all the users are using the same Front End -- you need to change
that. *Each user should have an individual copy of the front end. *Of
course, now you introduce deployment/distribution issues. *This has been
an on-going/age old problem in the Access community -- trying to use
Access in a Multi-User/Corporate environment. *Thus came the emergence
of the .Net environment which specifically addresses and solves these
issues. *

When you are talking Microsoft Server DB's and Multi-User environment --
you should be thinking "I need something that was specifically designed
for this kind of environment - I realize that Access is a file based
RDBMS and thus not the most ideal solution for a Server DB/Multi-user
based project".

In the meantime, I would go with the Individual copies of the Front end
for each user. *This is the alternative to the main solution of stepping
up to the .Net environment for this kind of project.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Thanks guys. I think I may have found the problem. I was updating
the foreign key for tblMedicalTraits in the Form_Current event. I
moved it to the Form_BeforeUpdate event and now the problem appears to
be gone.

This is in the development phase right now but yes, every user will
have their own front-end. We've used Access for many years but are
considering using Visual Studio for our front-ends for reasons you
gave and because of the persistent rumor that VBA will be replaced by
VB.Net. I'm currently doing some test development in VB.Net.