• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

Need help with form filter syntax in microsoft access

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
linbayzak
Asked:
linbayzak
  • 12
  • 12
1 Solution
 
Rey Obrero (Capricorn1)Commented:
change this

tmpClient = rs2("ClientNumber")


to

tmpClient = rs("ClientNumber")
0
 
linbayzakAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
what is the rowSource of the listbox?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
linbayzakAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
then you are getting the wrong clientnumber from the textbox.

how do you assign the client number to the hidden textbox?
0
 
linbayzakAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
can you attach your db.
0
 
linbayzakAuthor Commented:
I tried to once before and I got an error because of the extension (accdb) any ideas how to get past this? Thanks.
0
 
Rey Obrero (Capricorn1)Commented:
create a .mdb version - and i can't look at the .accdb now. no a2007
0
 
linbayzakAuthor Commented:
I am trying to do that, but I still get the accdb extension.  How can I do this.  Thanks.
0
 
Rey Obrero (Capricorn1)Commented:
create a new db and save as .mdb
0
 
linbayzakAuthor Commented:
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
 
linbayzakAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
see if this will do what you want
Database1rev.zip
0
 
linbayzakAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
look at the codes in the Private Sub cmbAddCaseExistClient_Click()
you have so many redundant codes
0
 
linbayzakAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
where are you taking up the masters degree?
0
 
linbayzakAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
are you taking up online classes? or physically attending the class?
0
 
linbayzakAuthor Commented:
online, have you ever taken them?
0
 
Rey Obrero (Capricorn1)Commented:
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
 
linbayzakAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now