Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 22nd, 2007, 05:09 AM
Authorized User
 
Join Date: May 2007
Location: , , .
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default Form Corruption / Problems with Where Clause

Hi Folks,

I've got a problem with Access 2000 in regard to forms opening up from other filter forms.

I am using the standard access auto-generated code for open form with the link criteria being the autonumber primary key. The form seems to randomly decide not to load up the appropriate record even though it transfers the filter command when you flick to design view.

The form in question work fine for a while until I do some changes to it on occasion; other times it just randomly decides to do this and I am forced to rebuild the form and set up all the code again.

The database is split front and back end and the issue I'm working on is in the development version not the current one people are using.

The problem is that the form won't filter on open to the correct record number. Allow Filters is set to Yes and has never been altered. I set me.filter="" in the code for form close a while back and that seemed to solve it for ages. The issue now has resurfaced.

At the moment the only solution i can think of at the moment is to open the form and then set the record source to "SELECT * FROM tablexyz WHERE id = ' me.id '"

This seems to be a cumbersome solution to this issue, i'm hoping you can help me out here.

 
Old October 22nd, 2007, 07:06 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I don't think that will work since the ID field will have to be set already.

What does the code that opens the form look like? Can you post that?

Assuming you are using a combo box to allow users to select a record, and the combo is bound to the PK field, and that is an integer, it should look like:

Dim iPK As Integer
Dim sDocName As String
Dim sLink As String

iPK = Me.cboPKPicker

sLink = "[PKField] = " & iPK

sDocName = "frmYourFormName"

DoCmd.OpenForm sDocName, , , sLink

I think that is the correct place for the Where clause.

Did that help?


mmcdonal
 
Old October 22nd, 2007, 07:55 AM
Authorized User
 
Join Date: May 2007
Location: , , .
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi MMc

As you can see from below that its the same code as you put above. I know the code below works as when I create a new form, copy the fields from the "bust" one across along with the code, the new form works fine. My question is really what is causing the corruption and if there's an alternative to making the form recordsource an sql each time which will increase the traffic on the network.

The form "fileinspect" source at the moment is tblfileinspect rather than sql.

The gstrcallingform in the code below is a global to reference back to the form which has the button on it and doesn't affect this as it just passes into a local variable at the new form and is then reset.

Private Sub btnopnarcases_Click()

    On Error GoTo Err_btnopnarcases_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    gstrcallingform = Me.Parent.Name

    stDocName = "frmfileinspect"

    stLinkCriteria = "[inspectid]=" & Me![inspectid]
    DoCmd.openform stDocName, , , stLinkCriteria

Exit_btnopnarcases_Click:
    Exit Sub

Err_btnopnarcases_Click:
    generalerrorhandler Err.Number, Err.Description, fsarc, "btnopnarcases_Click"
    Resume Exit_btnopnarcases_Click
    Resume

End Sub

 
Old October 22nd, 2007, 08:05 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I am not sure what would be causing "corruption." I don't think it is corrupted from what you say is happening. More like the form gets confused sometimes becuase the parameters are not passed. Try this for best practices sake:

Dim iInspectID As Integer
Dim sDoc As String
Dim sLink As String

If IsNull(Me.InspectID) or Me.InspectID = 0 Then
   MsgBox "Please select an Inspect Item", vbCritical '?
   Exit Sub
Else
   iInspectID = Me.InspectID
End If

gstrcallingform = Me.Parent.Name

sDoc = "frmfileinspect"

sLink = "[inspectid]=" & iInspectID
DoCmd.openform sDoc, , , sLink

Access gets funky sometimes when you try to post a value to a string or use it in a command without first putting it into a variable. So that might be it ("[inspectid]=" & Me![inspectid] is not good).

I am not sure what you mean by this: "and if there's an alternative to making the form recordsource an sql each time which will increase the traffic on the network."

You want to change the recordsource to increase network traffic?


mmcdonal
 
Old October 22nd, 2007, 09:15 AM
Authorized User
 
Join Date: May 2007
Location: , , .
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The square brackets bit is Microsoft just making allowances for poor fieldname design and someone putting spaces between the letters in the name of a control.

The code was generated for simplicity from the build button wizard.

The code in the IF statement would work for a single form, just not with a continuous form which these all run from.

I was thinking of the following code (rough script here not exact)

private sub btnviewinspect()

dim stformname as string
dim stlinkcriteria as string
dim strsql as string

stlinkcriteria=me.inspectid
stformname="frmfileinspect"

strsql="SELECT * FROM tblfileinspect WHERE inspectid = " & stlinkcriteria & " ;"

docmd.openform stdocname
forms!frmfileinspect.recordsource=strsql

However with up to 30 users at once hitting the fileinspect table it gets fun in terms of traffic load


 
Old October 22nd, 2007, 09:25 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Is InspectID a string or integer? If Integer, you need to do this:

private sub btnviewinspect()

dim stformname as string
dim stlinkcriteria as Integer
Public strsql as string

stlinkcriteria=me.inspectid
stformname="frmfileinspect"

strsql="SELECT * FROM tblfileinspect WHERE inspectid = " & stlinkcriteria 'you don't want the closing semi colon here.

docmd.openform stdocname

Put this line on the On Load event of the opening form:
If strsql <> "" Then
   Me.recordsource=strsql
End If

Make sure the recordsource can accept the string as the recordsource.

Also, you may want to consider using Pascal case since that is the default Access case, like strSql or strSQL, or stLinkCriteria.



mmcdonal
 
Old October 22nd, 2007, 09:51 AM
Authorized User
 
Join Date: May 2007
Location: , , .
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks mmc

I'll get back to you if the form keeps getting "confused"

 
Old October 22nd, 2007, 09:52 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Yeah, that is a technical term if you want to use it in front of other developers. =)

mmcdonal




Similar Threads
Thread Thread Starter Forum Replies Last Post
Problems using case statement in where clause vghiya SQL Server 2000 3 May 28th, 2007 05:12 AM
C variable in WHERE clause problems turnbui MySQL 1 December 7th, 2006 12:55 PM
Database corruption? Need suggestions, please. sweaver Access 2 April 24th, 2006 12:59 PM
form opening with where clause ginoitalo Access 4 December 22nd, 2004 09:16 PM
Facing Problems with text in a Where Clause mhosneddine Access 2 October 26th, 2003 04:10 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.