?
Solved

Access combo box for adding new data

Posted on 2011-04-23
9
Medium Priority
?
653 Views
Last Modified: 2012-05-11
Hi
Want to create a form contain a subform.
Main form contain fields with combo box where data can be selected from the list of each combo box or add new data
And the sub form also have some combo box some text box data can be choose for that table
Main and sub form is one to multi record relation.
While open the form it will always display with blank(to add new record in table).
Then allow me to add/choice from combo box or text box.

I am using Access 2003, if possible please provide me example that can help me to create it.
Thanks for you understanding and co-operation.
0
Comment
Question by:alam747
  • 5
  • 3
8 Comments
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35456540
The answer to the question in your title is to create code for the NotInList event of a combo box, to allow adding new records to the lookup table that is the combo box's row source.  Here is code for that:

Private Sub cbo________NotInList(strNewData As String, intResponse As Integer)
'Set Limit to List to Yes
'Created by Helen Feddema 7-Apr-2010
'Last modified 7-Apr-2010
'See Add-to Combo Boxes (AA 161).mdb

On Error GoTo ErrorHandler
   
    Dim cbo As Access.ComboBox
    Dim dbs As DAO.Database
    Dim intMsgDialog As Integer
    Dim intResult As Integer
    Dim rst As DAO.Recordset
    Dim strEntry As String
    Dim strFieldName As String
    Dim strMsg As String
    Dim strMsg1 As String
    Dim strMsg2 As String
    Dim strTable As String
    Dim strTitle As String
  
    'The name of the table that is the combo box's row source
    strTable = "________________"
  
    'The type of item to add to the table
    strEntry = "_____________"
  
    'The field in the lookup table in which the new entry is stored
    strFieldName = "_______________"
  
    'The add-to combo box
    Set cbo = Me.ActiveControl

    'Display a message box asking whether the user wants to add
    'a new entry.
    strTitle = strEntry & " not in list"
    intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
    strMsg1 = "Do you want to add "
    strMsg2 = " as a new " & strEntry & " entry?"
    strMsg = strMsg1 + strNewData + strMsg2
    intResult = MsgBox(strMsg, intMsgDialog, strTitle)

    If intResult = vbNo Then
        'Cancel adding the new entry to the lookup table.
        intResponse = acDataErrContinue
        cbo.Undo
        GoTo ErrorHandlerExit
    ElseIf intResult = vbYes Then
        'Add a new record to the lookup table.
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(strTable)
        rst.AddNew
        rst(strFieldName) = strNewData
        rst.Update
        rst.Close
   
        'Continue without displaying default error message.
        intResponse = acDataErrAdded
     End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35456547
However, your description is of an entirely different situation, a main form-subform type form.  For that, first set up the one-to-many link in the Relationships diagram, then make a main form bound to the "one" table, and a subform bound to the "many" table.  Place the subform on the main form in design view; it should have the linking key field for its LinkChildField and LinkMasterField properties.  You can use combo boxes on either the main form or subform to select values for fields in a record.  You can also place a record selector combo box in the form header, for selecting a record in the main form.  Here is code for that combo box (its row source is the main table, or perhaps just its key field and one other field for making the selection):
Private Sub cboSelect_AfterUpdate()
'Written by Helen Feddema 29-Jun-2009
'Last modified 11-Feb-2010

On Error GoTo ErrorHandler

   Dim strSearch As String

   'For text IDs
   strSearch = "[______ID] = " & Chr$(39) & Me.ActiveControl.Value _
      & Chr$(39)

   'For numeric IDs
   strSearch = "[______ID] = " & Me.ActiveControl.Value

   'Find the record that matches the control
   Me.Recordset.FindFirst strSearch

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 

Author Comment

by:alam747
ID: 35458068
Hi
Thanks for your advice.
If its possible to do using combo box wizered that would be easy for me to understand.
Thanks for your understanding and co-operation.

Thanks
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:alam747
ID: 35462769
Hi
I am wondering if anyone help me to find out the solution create the combo box for data entry purposes using combo box wizard.
Thanks
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35462811
Please explain exactly what you mean by a combo box for data entry purposes.  
0
 

Author Comment

by:alam747
ID: 35471378
Want to create a combo box which allowed to add new data. for example if I chose company from the existing list it will display the company address in the next field of address and the company name does not exist what I am looking for it will allow me to add new company name and company address .
Thanks
0
 

Accepted Solution

by:
alam747 earned 0 total points
ID: 35471405
Please advice how to do that using combo box wizard instead of VBA code if possible.
0
 

Author Closing Comment

by:alam747
ID: 36135812
Resolved by myself
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

569 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