Form Filter Help Needed 1

[Dragunsfyre]

I am using Access 2000. My database consists of client personal info data (name, address, SSN, DOB, etc) [tblClients] and is used primarily for document prep using Word 2000 mail merge documents. The primary key is the Client File Number [FileNumber]. I use a TAB CONTROL form to enter new / edit existing client information but I'm currently using the record selector arrows to scroll through the records until I find the one I want.

The first field on the first page of the form [frmPartyData] is [FileNumber]. I'd like to be able to enter the file number and, if it exists, filter the recordset to just that one record, edit it if I need to, and retain that current record to print the related documents (contract, letter, etc). If the file number does not exist, I'd like to be in Add Record mode to create a new record in the database, still limiting the recordset to that record for document prep purposes. Any help is appreciated.

 

[Remou]

Hi
How about something like this, to select your data (where txtFileNumber is unbound) (?) :

Private Sub txtFileNumber_AfterUpdate()
'Dims etc  
Me.RecordSource = "Select * From [Table1] Where [FileNumber] = " & Me!txtFileNumber
  If Me.RecordsetClone.RecordCount = 0 Then
    vNewFileNumber = Me!txtFileNumber
    If MsgBox("Do you wish to create a new record" & vbLf _
        & "using this file number? " & vbLf _
        & "FILE NUMBER: " & vNewFileNumber, vbYesNo) = vbYes Then
        DoCmd.GoToRecord , , acNewRec
        Me!FileNumber = vNewFileNumber 'hidden field
        Me!txtFileNumber = vNewFileNumber 'for neatness
    Else
        Me!Field1 = ""
    End If
  End If

End Sub

 

[Remou]

Oops
Select From tblClients

 

[Dragunsfyre]

I'm getting a Runtime Error = 3022.

The field, [FileNumber], is the primary key for [tblClients] and is indexed with no duplicates allowed.

When I debug, Me.Recordsource is returning [tblClients] and Me!txtFileNumber is returning the selected filenumber, but execution stops there.

Any thoughts or suggestions.

 

[Dragunsfyre]

If I had read the first sentence of your post a little more closely, I would have seen the reference to the form field [txtFileNumber] being unbound.

No matter what file number I enter, the Me.RecordSetClone.RecordSet argument always returns a 0 and the message box appears.

I modified the control on the form for FileNumber from a text box to a combo box to list all available file numbers to eliminate the chance of typos in entering filenumbers, but with the same result.

Still looking for answers.

 

[Remou]

Hi
Is this a typo?:
Me.RecordSetClone.RecordSet
did you mean
Me.RecordSetClone.RecordCount?

 

[Dragunsfyre]

That was a typo. I meant "Count" not "Set".

 

[Remou]

Hi
If you paste this:
[tt]Select * From [tblClients] Where [FileNumber] = 1[/tt]
(Choose appropriate file number)
into a query in SQL mode, does it return 0 records? I asked, because I tested the code before I posted, so I do not see why it is not working

 

[Dragunsfyre]

When I enter the query in SQL as

SELECT * From [tblClients] Where [FileNumber] = "05-003", the appropriate record returns.

Does the fact that the file number is stored as a text value rather than as a numeric value matter?

 

[Remou]

Yes, it does.
This line would change to:
Me.RecordSource = "Select * From [Table1] Where [FileNumber] = '" & Me!txtFileNumber & "'"

*Thinks* Must look at the other lines too...

 

[Remou]

Drat. "Select * From [tblClients] ...

 

[Dragunsfyre]

Geez. Works like a charm.

I've spent more hours trying to solve this one than I care to count. You've been a great help. Many, many thanks.

 

[Remou]

Thanks v much for the star. I hope you spotted this goof:
Else
Me!Field1 = ""
End If

Should be (say)
Me!txtFileNumber = "05-003