Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need help with form filter syntax in microsoft access

Posted on 2008-10-07
24
Medium Priority
?
375 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

971 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