asked on

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

Rey Obrero (Capricorn1)

change this

tmpClient = rs2("ClientNumber")

to

tmpClient = rs("ClientNumber")

linbayzak

ASKER

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

Rey Obrero (Capricorn1)

what is the rowSource of the listbox?

linbayzak

ASKER

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

Rey Obrero (Capricorn1)

then you are getting the wrong clientnumber from the textbox.

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

linbayzak

ASKER

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

Rey Obrero (Capricorn1)

can you attach your db.

linbayzak

ASKER

I tried to once before and I got an error because of the extension (accdb) any ideas how to get past this? Thanks.

Rey Obrero (Capricorn1)

create a .mdb version - and i can't look at the .accdb now. no a2007

linbayzak

ASKER

I am trying to do that, but I still get the accdb extension. How can I do this. Thanks.

Rey Obrero (Capricorn1)

create a new db and save as .mdb

linbayzak

ASKER

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.

linbayzak

ASKER

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

Rey Obrero (Capricorn1)

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

linbayzak

ASKER

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 :-)

Rey Obrero (Capricorn1)

look at the codes in the Private Sub cmbAddCaseExistClient_Click()
you have so many redundant codes

linbayzak

ASKER

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

Rey Obrero (Capricorn1)

where are you taking up the masters degree?

linbayzak

ASKER

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 :-)

Rey Obrero (Capricorn1)

are you taking up online classes? or physically attending the class?

linbayzak

ASKER

online, have you ever taken them?

Rey Obrero (Capricorn1)

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/

linbayzak

ASKER

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