Results 1 to 10 of 10
  1. #1
    Keeyter is offline Novice
    Windows 7 Access 2007
    Join Date
    Apr 2010
    Posts
    8

    Please Help, Combo Box Query Form Table Issue

    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.

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    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

  3. #3
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    is your problem solved if yes mark this thread solved.

  4. #4
    Keeyter is offline Novice
    Windows 7 Access 2007
    Join Date
    Apr 2010
    Posts
    8
    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

  5. #5
    Keeyter is offline Novice
    Windows 7 Access 2007
    Join Date
    Apr 2010
    Posts
    8
    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.

  6. #6
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Zip your database make sure it is in access 2000 format. Let me see.

  7. #7
    Keeyter is offline Novice
    Windows 7 Access 2007
    Join Date
    Apr 2010
    Posts
    8
    Sure thing

  8. #8
    Keeyter is offline Novice
    Windows 7 Access 2007
    Join Date
    Apr 2010
    Posts
    8
    That bad huh?

  9. #9
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    I am looking at it right now.

  10. #10
    Keeyter is offline Novice
    Windows 7 Access 2007
    Join Date
    Apr 2010
    Posts
    8
    Should I start from scratch or scratch the whole idea/project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Issue with combo box in form
    By 57chevy in forum Access
    Replies: 1
    Last Post: 01-05-2010, 07:27 AM
  2. Combo box Issue
    By desireemm1 in forum Access
    Replies: 2
    Last Post: 09-06-2009, 03:56 PM
  3. Make Table Query Issue
    By tmcrouse in forum Access
    Replies: 0
    Last Post: 07-23-2009, 03:20 PM
  4. Table/Form Issue
    By ginap in forum Access
    Replies: 3
    Last Post: 06-17-2009, 01:12 PM
  5. Need help with query table issue using forms
    By KLynch0803 in forum Access
    Replies: 1
    Last Post: 06-02-2009, 10:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums