Look up Query

[Cumminsc]

Hello, I have a look up on my Contact Details form but when I try to put in a new one it wont let me, so I go into the property sheet to change the Limit to List and I get an error because my column width property is set to 0. If I set it to 2.542 then the drop down only picks up the reference MCC001 instead of the company name Covey McCormick.. What have I done wrong here?

:banghead:

 

[RainLover]

Hello, I have a look up on my Contact Details form but when I try to put in a new one it wont let me, so I go into the property sheet to change the Limit to List and I get an error because my column width property is set to 0. If I set it to 2.542 then the drop down only picks up the reference MCC001 instead of the company name Covey McCormick.. What have I done wrong here?

Put in a new what?
You changed "Limit to List" from what to what?

It looks like your Bound Column is the Primary Key which is a Number not Text. And this would be correct.

Are you wanting to add a new item/record to the List.

Could you explain what you want and post a copy in 2003. We all don't drive the latest.

 

[Cumminsc]

I tried to change limit to list from yes to no as I want to be able to add new companies via the contact form.

Yes I want to add a new company to the list.

 

[RainLover]

You can't do it there.

As the name says it is a Look up and that is what it does.

You can either create a New Form to add a new Item. This form could also be used to edit spelling mistakes etc. This is what I normally use. Or you can add a new item straight to the Table.

In either case you will need some code.

I will find it and post back. Don't go away.

 

[RainLover]

This one requires that you have a separate Form. In this case it is called frmWorkOrderDetails.

Naturally you would change the Names in this code to suit your situation.

Place this code behind the Not On List event of the Combo Box.

Private Sub WorkOrderNumber_NotInList(NewData As String, Response As Integer)
Dim intNewCategory As Integer, intTruncateName As Integer, strTitle As String, intMsgDialog As Integer
    ' Display message box asking if user wants to add a new category.
    strTitle = "This is a New Work Order. Must Create,"
    intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
    intNewCategory = MsgBox("You Must add this New Work Order. Continue?", intMsgDialog, strTitle)
    If intNewCategory = vbYes Then
        ' Remove new SubSection from SubSection combo box so
        ' control can be requeried when user returns to form.
        DoCmd.RunCommand acCmdUndo
        
        DoCmd.OpenForm "FrmWorkOrderDetails", acNormal, , , acAdd, acDialog, NewData
        ' Continue without displaying default error message.
        Response = acDataErrAdded
    End If
End Sub

 

[Cumminsc]

ok, I am not going anywhere yet.

Thanks very much for your help with this. I am going to buy a book for dummies!

 

[RainLover]

This version writes immediately after answering yes to a message box.

Private Sub cmbZipcode_NotInList(NewData As String, Response As Integer)
    intAnswer = MsgBox("The postal code " & Chr(34) & NewData & _
    Chr(34) & " is currently not found in this database." & vbCrLf & _
    "Would you like to add it to the database?" _
    , vbQuestion + vbYesNo, "Address Type Not Found")
    
    If intAnswer = vbYes Then
        strSQL = "INSERT INTO tblZipcodes([zipCode], [cityID]) " & _
                 "VALUES ('" & NewData & "', '" & Me.cmbCity & "');"
       
        DoCmd.RunSQL strSQL
     
        MsgBox "The new postal code has been added to the database." _
            , vbInformation, "New postal code added"
        Response = acDataErrAdded
    Else
        MsgBox "Please choose a job title from the list." _
            , vbInformation, "Zip code not added, please reselect the zip code from the list"
        Response = acDataErrContinue
    End If
End Sub

Again change Names to suit your situation.

 

[Cumminsc]

Pardon, that's way too technical for me RainLover! I am signing out and getting a book for dummies. I will arrange to have my account closed as I am not getting very far with this website as you are all way too clever for me!

Sorry and thank you for your time.

 

[RainLover]

If you post your DB in 2003 I will do it for you, tomorrow.

Don't run away just yet. You just have to get over the initial learning stage.