Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help with form filter syntax in microsoft access

Posted on 2008-10-07
24
Medium Priority
?
371 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 12
24 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22702222
what is the rowSource of the listbox?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

670 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