Please Help with Coding! (1 Viewer)

[MysticElaine]

Hello. I am very new to Access (just opened the program for the first time on 5/21/14). I am creating a project for work, which is coming along pretty well. I am stuck though on populating form1 from form2 after selecting an item in a listbox as well as getting the listbox to filter correctly. I need to do this in a couple of different areas. Here is my project in a nut shell

Form1 "Start" has 2 buttons, New Entry and Search.
When I click New Entry, it opens up Form 2 "frmCases", which is the main form, I guess. From here, I enter lots of data. At one point, when I click on the Client text box, it opens a third form "frmClients." This form has 3 text boxes, First Name, Last Name, and DOB. These text boxes are to not only add a client to the "Clients" table, but also are used to search the "Client List" listbox. I also have a text box that just has the concatenate of the First and Last name, which I was using to populate the previous form. What I wanted, was to make sure that the client being added was actually new. So if as they were typing in the information they saw the name pop up in the listbox, they would then click that name instead of clicking the add new client button. Regardless of if they clicked the add new client button or clicked the list box, I wanted the client's name and unique number to then be populated in the "frmClients" form.

Last night I had gotten the search boxes to sort of work, where when I typed the first name, the list box filtered, but when I typed the last name, it didn't finish filtering but also added first names with the same last name. I had also managed to get the Client name populated in the form I wanted when I clicked the add new client button. I could never get the selection from the list box to populate. Here is my code for these:

Private Sub First_Name_Change()
Dim ClSearchSQL As String
ClSearchSQL = "SELECT Clients.[First Name], Clients.[Last Name], Clients.[DOB] FROM Clients"
ClSearchSQL = ClSearchSQL & " WHERE (((Clients.[First Name]) Like '" & Me.[First Name].Text & "*'))"
ClSearchSQL = ClSearchSQL & " ORDER BY Clients.[First Name]"
Me.[Client List].RowSource = ClSearchSQL
End Sub

Private Sub Form_Load()
[Client Name].Visible = False
End Sub

Private Sub Add_New_Client_Button_Click()
Dim ClName As Variant
ClName = Me![Client Name].Value
DoCmd.Close acForm, "frmClients"
Forms![frmCases]!Client.Value = ClName
Forms![frmCases]![Language Requested].SetFocus
End Sub

Private Sub Last_Name_Change()
Dim LNSearchSQL As String
LNSearchSQL = "SELECT Clients.[First Name], Clients.[Last Name], Clients.[DOB] FROM Clients"
LNSearchSQL = LNSearchSQL & " WHERE (((Clients.[Last Name]) Like '" & Me.[Last Name].Text & "*'))"
LNSearchSQL = LNSearchSQL & " ORDER BY Clients.[First Name]"
Me.[Client List].RowSource = LNSearchSQL
End Sub

I still can't seem to figure out how to populate the form "frmCases" from the listbox selection. I have to do this with another form as well, which was a bit simpler so I was trying it there first. From my "Start" form, I could click the Searches button and it opens up the form "Search Cases." This form is simple and has one text box "Search Case" to find a case, which filters the "Search Cases List" listbox. I want to select the case from the list box, hit the edit button, and have it populate the information into the "frmCases". The most I got was for it to ask me to input an ID number, that was with this code:

Private Sub Edit_Existing_Case_button_Click()
Dim CaseList As Control, ExCaseID As Long
Set CaseList = Me![Search Cases List]
ExCaseID = CaseList.Column(2)
DoCmd.Close acForm, "Search Cases"
DoCmd.OpenForm "frmCases", acNormal, , "Forms![frmCases]![Case ID].Value=ExCaseID", acFormEdit
End Sub

I had also tried going to "frmCases" and just entering the ID control source as =Forms![Search Cases]![Search Cases List].Column(2). It worked for the first time, but when I tried again, it just stayed on that form, it wouldn't repopulate for a different selection.

Sorry for the long post. These are the only two issues I am having at the moment from getting the project completed, which I wanted to have done by Tuesday. Thank you for any and all help, it is appreciated. As I mentioned, I am brand new to Access, so whatever your reply, I would appreciate an explanation along with whatever answers you give so I can learn and not just have something to copy and paste. Thanks!

 

[Reenen]

I assume your DB isn't huge. Perhaps you can zip it and attach? (I'm new here, so I dunno if that is allowed, but it should be).

 

[MysticElaine]

I have attached it. Hope it helps.

 

[Reenen]

Ok, I have it open, but I am struggling to understand what you are trying to achieve. Please ask your questions in small chunks. (I have your DB now so I can go wherever your reference me).

It feels to me that your UI is deceptive. IE you are hiding things, while you could just be enabling/disabling them.

 

[MysticElaine]

Ok, so this program is designed for all users in the company to use forms to input information, but they can't access anything else. So when it starts, the "Start" form will first appear. Lets say they want to edit a case that is "Pending." They would click on the "Search" button. This brings them to the form "Search Cases." From here, I want them to be able to search the client's name in the search box, see it on the listbox, select the name in the listbox and then click the button to edit that case. When they click the button, it closes the "Search Cases" form as we are done with it and opens the main form "frmCases" with the information populated.

I am having problems populating the "frmCases" form. I thought if I made the bound column of the listbox the unique Case ID number, and have it sent to that field in the "frmCases" form, the form would then populate the rest of the information.

This was the second part of my original post. The code I have right now doesn't seem to pull the bound column value. The form just closes and asks me to input a value.

Thanks.

 

[Reenen]

See attached file (form1)

I created an *unbound* combo box, and then set some code in the event to set the current record.

You probably still want to have save record etc buttons.

I hope this is what you were looking for. Basically the code changes the "active record" when you select it via the unbound combo box.

Private Sub Combo0_AfterUpdate()
DoCmd.GoToRecord acDataForm, "Form1", acGoTo, Combo0.ListIndex + 1
End Sub

 

[MysticElaine]

Hi, thanks for that. That is sort of what I want. The other fields are populating due to the case ID number. However, I don't know the case ID right off the bat. So in another form, I wanted to search for the specific case (searched by name), then choose the case I wanted, and then after choosing it populates the first form.

So how to get the record I chose in the second form to populate in the first form?

Thanks.

 

[RainLover]

Mystic

You are going a bit fast for your knowledge. Eventually you will become completely frustrated because of too many little mistakes that add up to become something serious.

I will attach some things that may be of help. I hope you can take the time to read them thoroughly.

These files are in 2003 and 2007. You should be able to read them.

The Database is different from what you have but does what you want. You just have to adapt it.

 

[MysticElaine]

Hi, Thanks. I will read over that information. I have actually done a ton of reading, doing as many tutorials as I can find and also finding a Coding for Dummies online book. I realize my knowledge is very little as I just started last week, but my work place needs this project in place ASAP and no one really knows how to do it, so my manager and I have been trying to create one to work for our purpose. Thanks again.

Mystic

You are going a bit fast for your knowledge. Eventually you will become completely frustrated because of too many little mistakes that add up to become something serious.

I will attach some things that may be of help. I hope you can take the time to read them thoroughly.

These files are in 2003 and 2007. You should be able to read them.

The Database is different from what you have but does what you want. You just have to adapt it.