Question Add new record to form!

[Alan#]

Hi Guys
I'm new to access and wondering can anybody help me, i have a customer form with a combobox (cboCode) and a text box (txtName).
The textbox is bound to the combobox ( =[cboCode].[column](1) ) However i want to still be able add a new record for a customer. Is this possible this way or do i have to do it a totally different way?? Any help is appreciated!

 

[pr2-eugin]

Hello Alan#, Welcome to AWF

Your description does not make complete sense, is this ComboBox used to Filter the Form, which is bound to the Customer Table?

 

[Alan#]

Thanks for reply Paul, yes the combobox is used to filter the form and it is bound to the customer table!

 

[pr2-eugin]

So basically you want to add a record if there is no client in the ComboBox? If yes, then you can have a button next to the ComboBox, saying add record, then you can code the button to go to a New Record, like.

Private Sub newRecBtn_Click()
    DoCmd.GoToRecord Record:=acNewRec
End Sub

 

[Alan#]

In the combo when you click on the drop down there's two columns, code and customer which are pulled from the customer table, if you select an option from the dropdown it will only be the code that will stay in the combo box and the customer name will automatically be filled into the textbox. The combo box is blank when the form opens so it does accept input for a new code and will save it but when i go to input in the text box it says "Control can't be edited it's bound to the expression [cboCode][column]1" sorry if i havent been clear or answered your questions properly!

 

[pr2-eugin]

Is this ComboBox Bound/UnBound? You said they are used for filtering, which makes the ComboBox UNBOUND, but now you are saying you need it to add data to the table?

When you say the ComboBox is blank when the Form opens, what do you mean? The Drop Down has no records in it?

Yes, the TextBox cannot be edited, because it looks for its data in the ComboBox. Which in turn looks for its data from the Customers tables.

So somewhere along the lines I am lost. So could you explain in simple English, no access terminology, just simply explain what you want to do?

 

[Alan#]

Yea i'd say you need a bit of patience for people like me The drop down has records, two columns code and name, its just blank when the form opens like normal. The combo is bound its control source is Code. I think what im trying to do is not possible but anyway, i want to add a new customer which means inputing a new code in the combo which i can do and then input a customer name in the text box which i can't because it cannot be edited. Basically this form is for a skip hire company and i want them to be able to see the list of existing customer codes and names from the drop down, which is working fine but i also want them to be able to add new customers as well.

 

[pr2-eugin]

Show the code associated with the ComboBox, probably in the AfterUpdate event of the Combo Box.

Apart from that, this ComboBox should be UNBOUND.

 

[Alan#]

Okay i have unbound the combo. The combo box was initially set up by the wizard. There are no event proceedures. The row source code is SELECT Customers.Code, Customers.Name FROM Customers ORDER BY Customers.

;

 

[pr2-eugin]

Okay, now we are getting somewhere, use this link as a guide to set up your combo box to filter the Form. http://allenbrowne.com/ser-03.html

In the rs.NoMatch, the code will dictate to display a message, you can edit to something like.

:
        If rs.NoMatch Then
            [COLOR=Green]'Original Code : MsgBox "Not found: filtered?"[/COLOR]
            [B]If MsgBox("Not found: Do you wish to add this record?", vbYesNo+vbQuestion) = vbYes Then DoCmd.GoToRecord Record:=acNewRec[/B]
        Else
[COLOR=Green]            'Display the found record in the form.[/COLOR]
            Me.Bookmark = rs.Bookmark
        End If
:

 

[Alan#]

Hey Paul thanks for your help, as it turns out it was just a simple bit of code in the after update of the combobox (cboCode) txtName = [cboCode][column](1) and then bound it to Name:banghead: