Solved

Need help with form filter syntax in microsoft access

Posted on 2008-10-07
24
343 Views
Last Modified: 2013-11-28
Hi Experts,
I have a command "Add Case for Existing Client" that is proving to be quite difficult.  The user wants some of the demographic area to autopopulate the form but the record is a new case (CaseNumber).  So, the client can have several different cases but maintains the same client id (ClientNumber).  Below is what I have, I am getting an error Runtime Error 3022...duplicate index issue.  The ClientNumber cannot have duplicates in the table structure.  I am wondering if this is my problem.  The CaseNumber is an autonumber field.  I have the client number set to no duplicates to prevent overwriting the client number.  Help?  This is way over my head.  Thanks.

The line where the code breaks is almost at the end:
me.filter = "[ClientNumber]=" & tmpClient
me.filteron=true


Laura
Private Sub cmbAddCase_Click()
 
 

    Dim rs As Recordset

    Dim rs2 As Recordset

    Dim tmpClient As Integer

    

   

    Set rs = CurrentDb().OpenRecordset("Select * from tblGenesisHouseClientInfo Where ClientNumber = " & Me.txtClientNumber)

    Set rs2 = CurrentDb().OpenRecordset("Select * from tblGenesisHouseClientInfo")

    

    rs2.AddNew

    

    rs2("SuspectedChildAbuse") = rs("SuspectedChildAbuse")

    rs2("ChildrenInvolved") = rs("ChildrenInvolved")

    rs2("ChildrenInShelter") = rs("ChildrenInShelter")

    rs2("LastName") = rs("LastName")

    rs2("FirstName") = rs("FirstName")

    rs2("MInitial") = rs("MInitial")

    rs2("AgeForm2") = rs("AgeForm2")

    rs2("GenderForm2") = rs("GenderForm2")

    rs2("CaseDate") = rs("CaseDate")

    

    

    

    rs2.Update

    tmpClient = rs2("ClientNumber")

    

    DoCmd.OpenForm "frmClientInformationP2", , , "ClientNumber=" & tmpClient, acFormEdit

    'Forms!frmClientInformationP2.txtClientName = rs("[ClientFirstName]" & "" & "[ClientMiddleInitial]" & "" & "[ClientLastName]")

    Forms!frmClientInformationP2.chkSuspChiAbu = rs("SuspectedChildAbuse") = True

    Forms!frmClientInformationP2.txtNumberOfChildren = rs("ChildrenInvolved")

    Forms!frmClientInformationP2.txtNumberInShelter = rs("ChildrenInShelter")

    

    'DoCmd.OpenForm "frmClientInformationP1", , , "CaseNumber=" & List10, acFormEdit

    Me.Filter = "[ClientNumber]=" & tmpClient

    Me.FilterOn = True

    

    

    Set rs = Nothing

End Sub

Open in new window

0
Comment
Question by:linbayzak
  • 12
  • 12
24 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22693988
change this

tmpClient = rs2("ClientNumber")


to

tmpClient = rs("ClientNumber")
0
 

Author Comment

by:linbayzak
ID: 22702195
That brings up a new case but for the wrong client.  For some reason, no matter what client I choose from the listbox, it brings up the same client.  For example, if I click on Susan Jefferson, it brings up Sarah Smith, If I click on Tara Reagan, it brings up Sarah Smith.  It's almost as if a filter is being stored somewhere.  Any ideas?  

On the form, I have a hidden textbox that stores the client number, when I make a selection and it brings up the wrong client (Sarah Smith, client number 209), the correct client number I chose (206) is in the hidden box.  

Thanks :-)
Laura
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22702222
what is the rowSource of the listbox?
0
 

Author Comment

by:linbayzak
ID: 22702233
SELECT tblGenesisHouseClientInfo.ClientNumber, tblGenesisHouseClientInfo.CaseNumber, tblGenesisHouseClientInfo.ClientLastName, tblGenesisHouseClientInfo.ClientFirstName, tblGenesisHouseClientInfo.ClientMiddleInitial, tblGenesisHouseClientInfo.CaseDate FROM tblGenesisHouseClientInfo WHERE Year([CaseDate])=Forms!frmGenesisHouseSearchResults!cboYear ORDER BY tblGenesisHouseClientInfo.ClientNumber;

Bound column is 2 (which should be case number)

Thanks :-)
Laura
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22702285
then you are getting the wrong clientnumber from the textbox.

how do you assign the client number to the hidden textbox?
0
 

Author Comment

by:linbayzak
ID: 22702304
Me.txtClientID = Me.List10.Column(0, Me.List10.ListIndex)

Thanks,
Laura
Private Sub cmbAddCaseExistClient_Click()

    

    Dim rs As Recordset

    Dim rs2 As Recordset

    Dim tmpClient As Integer

    

    Me.txtClientID = Me.List10.Column(0, Me.List10.ListIndex)

    Set rs = CurrentDb.OpenRecordset("Select * from tblGenesisHouseClientInfo Where ClientNumber = " & Me.List10.Column(0))

    Set rs2 = CurrentDb().OpenRecordset("Select * from tblGenesisHouseClientInfo")

    

    rs2.AddNew

    rs2("ClientNumber") = rs("ClientNumber")

    rs2("ClientLastName") = rs("ClientLastName")

    rs2("ClientFirstName") = rs("ClientFirstName")

    rs2("ClientMiddleInitial") = rs("ClientMiddleInitial")

    rs2("Address") = rs("Address")

    rs2("City") = rs("City")

    rs2("State") = rs("State")

    rs2("County") = rs("County")

    rs2("Telephone") = rs("Telephone")

    rs2("Age") = rs("Age")

    rs2("Male/Female") = rs("Male/Female")

    rs2("Race") = rs("Race")

    rs2("Physical") = rs("Physical")

    rs2("Psychological") = rs("Psychological")

    rs2("Sexual") = rs("Sexual")

    rs2("AbusedAsChild") = rs("AbusedAsChild")

    rs2("WitnessedAbuse") = rs("WitnessedAbuse")

    rs2("Alcohol") = rs("Alcohol")

    rs2("DrugAbuse") = rs("DrugAbuse")

    rs2("Alcohol/DrugAbuse") = rs("Alcohol/DrugAbuse")

    rs2("Employed/student") = rs("Employed/student")

    rs2("PoliceIntervention") = rs("PoliceIntervention")

    rs2("EmergencyMedicalAssistance") = rs("EmergencyMedicalAssistance")

    rs2("Veteran") = rs("Veteran")

    rs2("NotesForPage1") = rs("NotesForPage1")

    rs2("MentalDisability") = rs("MentalDisability")

    rs2("MentalDisabilityNotes") = rs("MentalDisabilityNotes")

    rs2("PhysicalDisability") = rs("PhysicalDisability")

    rs2("PhysicalDisabilityNote") = rs("PhysicalDisabilityNote")

    rs2("AdultProtective") = rs("AdultProtective")

 

    

    

    

    rs2.Update

    tmpClient = rs("ClientNumber")

    

    AddNew = True

    

    DoCmd.OpenForm "frmClientInformationP1", , , "ClientNumber=" & Me.List10.Column(0, Me.List10.ListIndex)

    Forms!frmClientInformationP1.txtLastName = rs2("ClientLastName")

    Forms!frmClientInformationP1.txtFirstName = rs2("ClientFirstName")

    Forms!frmClientInformationP1.txtMiddInit = rs2("ClientMiddleInitial")

    Forms!frmClientInformationP1.txtClientStreetAdd = rs2("Address")

    Forms!frmClientInformationP1.txtCity = rs2("City")

    Forms!frmClientInformationP1.cboState = rs2("State")

    Forms!frmClientInformationP1.cboCounty = rs2("County")

    Forms!frmClientInformationP1.txtPhone = rs2("Telephone")

    Forms!frmClientInformationP1.txtAge = rs2("Age")

    Forms!frmClientInformationP1.cboGender = rs2("Male/Female")

    Forms!frmClientInformationP1.cboRace = rs2("Race")

    Forms!frmClientInformationP1.chkPhysical = rs2("Physical") = True

    Forms!frmClientInformationP1.chkPsychol = rs2("Psychological") = True

    Forms!frmClientInformationP1.chkSexual = rs2("Sexual") = True

    Forms!frmClientInformationP1.chkAbusedAsChild = rs2("AbusedAsChild") = True

    Forms!frmClientInformationP1.chkNewClient = rs2("NewClient") = True

    Forms!frmClientInformationP1.chkContinuing = rs2("ContinuingClient") = True

    Forms!frmClientInformationP1.chkWitnessedAbuse = rs2("WitnessedAbuse") = True

    Forms!frmClientInformationP1.chkAlcohAbuse = rs2("Alcohol") = True

    Forms!frmClientInformationP1.chkDrugAbuse = rs2("DrugAbuse") = True

    Forms!frmClientInformationP1.chkAlcohDrugAbuse = rs2("Alcohol/DrugAbuse") = True

    Forms!frmClientInformationP1.chkEmpStud = rs2("Employed/student") = True

    Forms!frmClientInformationP1.chkPoliceInterv = rs2("PoliceIntervention") = True

    Forms!frmClientInformationP1.chkEmergMedAssist = rs2("EmergencyMedicalAssistance") = True

    Forms!frmClientInformationP1.chkVeteran = rs2("Veteran") = True

    Forms!frmClientInformationP1.txtNotesP1 = rs2("NotesForPage1")

    Forms!frmClientInformationP1.chkMentDisab = rs2("MentalDisability") = True

    Forms!frmClientInformationP1.txtMentDisNotes = rs2("MentalDisabilityNotes")

    Forms!frmClientInformationP1.chkPhysDisab = rs2("PhysicalDisability") = True

    Forms!frmClientInformationP1.txtPhysDisNotes = rs2("PhysicalDisabilityNote")

    Forms!frmClientInformationP1.chkAdultProtCase = rs2("AdultProtective") = True

    Forms!frmClientInformationP1.txtClientNumber = rs2("ClientNumber")

    

    Me.Filter = "[ClientNumber]=" & Me.List10.Column(0)

    'Me.Filter = Me.txtClientID

    Me.FilterOn = True

    

    Set rs = Nothing
 

End Sub

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22702497
can you attach your db.
0
 

Author Comment

by:linbayzak
ID: 22702508
I tried to once before and I got an error because of the extension (accdb) any ideas how to get past this? Thanks.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22702523
create a .mdb version - and i can't look at the .accdb now. no a2007
0
 

Author Comment

by:linbayzak
ID: 22702572
I am trying to do that, but I still get the accdb extension.  How can I do this.  Thanks.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22702588
create a new db and save as .mdb
0
 

Author Comment

by:linbayzak
ID: 22702609
I am trying to do that, but I don't get the prompt to save as so I can change the extension.  I only have Access 2007 on my computer.  I tried to change the default format to 2000-2003, but that didn't work either.  Sorry, I have been struggling with this ever since I put the 2007 on here. Some of our clients use 2003 and this has become somewhat of a problem for me.  So, learning how to do this will be great. Thanks.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:linbayzak
ID: 22702645
Got it, finally.  Here is the compressed file.  The form you and I have been working on it titled frmGenesisHouseSearchResults. It opens frmClientInformationP1. Thanks for your help.

Laura
Database1.zip
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22702689
create a copy of your .accdb.
change .accdb to .txt
if the name of the db is  myDB.accdb , rename it to myDB.txt

i may have sometime in the afternoon to look at it in a PC with A2007
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 22702940
see if this will do what you want
Database1rev.zip
0
 

Author Closing Comment

by:linbayzak
ID: 31503985
That works, capricorn1, thank you so much. You have no idea what this means to me.  So, can you tell me where my logic was off?  Thanks again, I really appreciate this.  

Laura :-)
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22703198
look at the codes in the Private Sub cmbAddCaseExistClient_Click()
you have so many redundant codes
0
 

Author Comment

by:linbayzak
ID: 22703226
K, I am struggling with logic.  I'm finishing up a masters degree in management (start my last class tomorrow) and when I am done, I am planning on taking some database & SQL courses to comb over the basics again and find out why I am having such a hard time putting the concepts into action.  Thanks for your help, I really appreciate it, I will be posting another question soon because my code on another form is causing an overwrite of the client number which could be really bad for our client.  Thanks again :-)
Laura
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22703252
where are you taking up the masters degree?
0
 

Author Comment

by:linbayzak
ID: 22703281
University of Phoenix. I did my Bachelors in Information Technology there as well.  It's been a long hard road, but I can see the light at the end of the tunnel.  If I could go back in time, I would have paid more attention to logic and architecture instead of just jumping into the fun part--writing code.  Do you have any suggestions for online database training?  I want to know this stuff upside down and backwards. Thanks :-)
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22703325
are you taking up online classes? or physically attending the class?
0
 

Author Comment

by:linbayzak
ID: 22703339
online, have you ever taken them?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22703378
nope.. i had planned before to get a masters degree in engineering, but got tied up and was not able to find time to do it.


try this site

http://www.functionx.com/access/

 
0
 

Author Comment

by:linbayzak
ID: 22703396
Thanks, capricorn1 and it's even free information.  I think I need to go back to the blackboard and get a firm understanding of the fundamental priciples. If I don't I am just going to get more confused.  

You should look into UOP, I highly recommend them.  The only downfall is lack of on-hands training which can be a problem in some courses.  

Thanks,
Laura
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now