Please Help, Combo Box Query Form Table Issue

[Keeyter]

I have tried to accomplish this seven ways of sunday but with my limited knowledge it is proving more tricky than my skills. Here is what I have and what I am trying to accomplish.

1. A Table with Customer Details
2. A Form "showing" customer details.

Desires;



1. From Combo box - Show dropdown of customers stored in said table.
2. On Selection of Customer from Combo box, customer fields on form populate with coordinating information from form. This combo box will also populate a Customer history that is tabbed to the same form from a table yet to be built.
2A. Or the ability to start typing and smart names come up, almost like a google search does.
3. Ability to edit customer from form which then updates/saves details to table.
4. I have a save, a save and new, a close, and a new button. When I click save, again information in form updates table. If I click save and new, a new Client ID autopopulates from the next available ID that is open from the Customer Details table.
5. Finally if any information has been changed and any button is clicked to navigate away, a popup asks if you want to save change. Such as " Changes have been made to the current customer, would you like to save or discard these changes?"


I know it looks like a laundry list but I wanted to explain it as well as possible. It is for sure above my meager ability. I thank anyone in advance who is kind enough to help someone who wants to learn.

[maximus]

here is what i have done;

I have a single table Client and a unbound form called Client. when you want to enter new data click on Create New Record. the Clientd ID is automatically updated with a number. then I have used record set to both edit and add new record. to add new record click o ne record button, enter data in the fields and click on Add/Edit Button Data is put into the table and combocox on the form with the names of client is updated. to edit existing record, select a client name in the combobox the data is loaded onto the form. make edits and click the add/edit button the edited data is put on the table. This takes care of most of the points on your check list. Now you try to improve on this any problems let me know.

Note: To enter new data please click on Create New Record button to generate the ID and then Type onto the fields otherwise your data will be lost.

Code used:
Private Sub Combo14_AfterUpdate()
Me.Text0 = Me.Combo14.Column(0)
Me.Text2 = Me.Combo14.Column(2)
Me.Text4 = Me.Combo14.Column(3)
Me.Text6 = Me.Combo14.Column(4)
Me.Text8 = Me.Combo14.Column(5)
Me.Text10 = Me.Combo14.Column(6)
End Sub

Private Sub Command12_Click()
On Error GoTo Err_Command12_Click
Dim intMySerialNumber As Integer
Me.Text0 = ""
Me.Text2 = ""
Me.Text4 = ""
Me.Text6 = ""
Me.Text8 = ""
Me.Text10 = ""
intMySerialNumber = IIf(IsNull(DMax("[ClientID]", "Client")), 1, DMax("[ClientID]", "Client") + 1)
Me.Text0 = intMySerialNumber
Exit_Command12_Click:
Exit Sub

Err_Command12_Click:
MsgBox Err.Description
Resume Exit_Command12_Click

End Sub

Private Sub Form_Dirty(Cancel As Integer)
If Me.Dirty Then
MsgBox "Please save data"
End If
End Sub

Private Sub Form_Load()
Me.Text0.Enabled = False
End Sub
Private Sub Command16_Click()
On Error GoTo Err_Command16_Click
Dim strSQL As String
Dim strOption As String
strOption = MsgBox("Do you want to create a new record Click Yes to proceed and no to abort", vbInformation + vbYesNo)
Select Case strOption
Case vbYes
strSQL = "Select * From Client Where ClientID=" & Me.Text0
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.EOF And rs.BOF Then
rs.AddNew
Else
rs.Edit
End If
rs!ClientID = Me.Text0
rs!ClientFirstName = Me.Text2
rs!ClientLastName = Me.Text4
rs!Address = Me.Text6
rs!ContactNumber = Me.Text8
rs!MobileNumber = Me.Text10
rs.Update
rs.Close
Set rs = Nothing
Me.Text0 = ""
Me.Text2 = ""
Me.Text4 = ""
Me.Text6 = ""
Me.Text8 = ""
Me.Text10 = ""
Case vbNo
MsgBox "New Record Canceled"
Me.Text0 = ""
Me.Text2 = ""
Me.Text4 = ""
Me.Text6 = ""
Me.Text8 = ""
Me.Text10 = ""
End Select
Exit_Command16_Click:
Exit Sub

Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click

End Sub

[maximus]

is your problem solved if yes mark this thread solved.

[Keeyter]

Sorry I have not had time to try your solution. I am looking forward to trying it tho as soon as I can spare a minute. Thanks Much

[Keeyter]

Okay after a week from the devil and a weekend of sickness I finally got time to start this.

As I started I found a problem. Apparently my table with Client Details is rather robust, it has a large amount of information. And inputting the Combo box limits me to 20 entries.

Seeing as I have a single billing address, multiple contact numbers, and multiple project address I am over the twenty limit for a combo box.

Any suggestions. You can look at what I have in the example I attached to my original posting. Thanks again and sorry for the delay.

[maximus]

Zip your database make sure it is in access 2000 format. Let me see.

[Keeyter]

Sure thing

[Keeyter]

That bad huh?

[maximus]

I am looking at it right now.

[Keeyter]

Should I start from scratch or scratch the whole idea/project.