Add dircetly to combo/list functionality

[fraser_lindsay]

I have a number of combo boxes linked to respective individual tables, for example tool combo look up the tool table which is composed of ToolID and Tool Type.

I want to be able to add additional tools directly into the combo box on the form when I come across a tool type that isn't in there already. However, my current combo tells me that I can't as I am trying to enter an item that isn't in the list.

So, if I then change the option on the combo on 'Limit to List' to No from Yes, it then gives me an error about column widths that need changing.

I don't want to do that but I do want to enter directly - can somebody point me in the right direction please.

 

[RuralGuy]

You need to leave the "Limit to List" property set to yes and put code in the NotInList event that will add the tool to the underlying query/table. From one of my cbo's:

Private Sub cboSupplier_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboSupplier_NotInList

'-- We may need to add another Supplier
    Response = MsgBox("[" & NewData & "] is not yet a Supplier..." & vbCr & vbCr & _
                    "Would you like to add a New Supplier to your DataBase?", vbYesNo)

    If Response = vbYes Then
        '-- Create a new Supplier record
        Dim db As DAO.Database
        Dim MySQL As String
        Set db = CurrentDb()
        MySQL = "Insert Into tblSuppliers(CoName,PriCat) " & _
                "Values(""" & NewData & """,27)"
        db.Execute MySQL, dbFailOnError
        Set db = Nothing
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If

Exit_cboSupplier_NotInList:
    Resume Next
    Set db = Nothing
    Exit Sub
    
Err_cboSupplier_NotInList:
    Call LogError(Err.Number, Err.Description, "cboSupplier_NotInList() in " & Me.Name)
    Resume Exit_cboSupplier_NotInList

End Sub

 

[fraser_lindsay]

Thanks Rural Guy, I'll add and then modify your code accordingly. It looks great.

Thanks again, I'll come back to you if I encounter any problems.

 

[RuralGuy]

No problem. Glad to help. Have fun!

 

[fraser_lindsay]

I'm back.

Here's my code:

Private Sub cboToolGroup_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboToolGroup_NotInList

'-- We may need to add another tool
Response = MsgBox("[" & NewData & "] is not listed as a tool type..." & vbCr & vbCr & _
"Would you like to this tool type to the Database?", vbYesNo)

If Response = vbYes Then
'-- Create a new tool group record
Dim db As DAO.Database
Dim MySQL As String
Set db = CurrentDb()
MySQL = "Insert Into tblToolType(ID,Tool Group) " & _
"Values(""" & NewData & """,27)"
db.Execute MySQL, dbFailOnError
Set db = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Exit_cboToolGroup_NotInList:
Resume Next
Set db = Nothing
Exit Sub

Err_cboToolGroup_NotInList:
Call LogError(Err.Number, Err.Description, "cboToolGroup_NotInList() in " & Me.Name)
Resume Exit_cboToolGroup_NotInList

End Sub


I get a compile error message which tells me:

'User defined type not defined'

It highlights this row in blue: db As DAO.Database

And the first row in yellow (is that standard when running the debugger?)

As you can see, I am a complete novice at this, but I try. What have I missed?

Cheers,

Fraser

 

[RuralGuy]

You have several problems that will come up. First, while viewing the code go to Tools>References and scroll down to Microsoft DAO 3.6 Object Library and put a check in the box on that line.

Next, replace this line:
Call LogError(Err.Number, Err.Description, "cboToolGroup_NotInList() in " & Me.Name)

with these two lines:
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description

Third, in this section:
MySQL = "Insert Into tblToolType(ID,Tool Group) " & _
"Values(""" & NewData & """,27)"

ID and [Tool Group] are the field names in your tblToolType table that you wish filled with the "Values". The "Values" *must* be in the same order as the field names mentioned in the line above and of the same DataType. In your case you have:
ID As TextField = the incoming NewData the user typed
[Tool Group] As Numeric = 27

Names with spaces or special characters *will* cause you unexpected grief and also need to be surrounded with [brackets]! It is *always* better to use CamelFontNames or Under_Score_Names.

That should get you a little further along in the code.

 

[fraser_lindsay]

Thanks very much for all your help, very much appreciated. Particularly my first proper guidance on coding anything.

I'll give that a bash shortly, hopefully it will all go according to plan.

Thanks again.

 

[fraser_lindsay]

Hello again,

I have modified my code to the following:

Private Sub cboToolGroup_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboToolGroup_NotInList

'-- We may need to add another tool
Response = MsgBox("[" & NewData & "] is not listed as a tool type..." & vbCr & vbCr & _
"Would you like to this tool type to the Database?", vbYesNo)

If Response = vbYes Then
'-- Create a new tool group record
Dim db As DAO.Database
Dim MySQL As String
Set db = CurrentDb()
MySQL = "Insert Into tblToolGroup(Tool_Group, ID) " & _
"Values(""" & NewData & """,27)"
db.Execute MySQL, dbFailOnError
Set db = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Exit_cboToolGroup_NotInList:
Resume Next
Set db = Nothing
Exit Sub

Err_cboToolGroup_NotInList:
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description

Resume Exit_cboToolGroup_NotInList

End Sub

However, I now when adding a new group and error message pops up to ask if I want to add it, as planned bbut when I say yes I get an error:

Error No: 20
Description: Resume without error

I have to force close Access as it won't disappear.

How do I fix this?

 

[john471]

Rural Guy - don't mean to intrude - just chucking this in 'cause fraser_lindsay is on-line now, and you are not, and (s)he may appreciate a speedy answer....

fraser_lindsay
the highlighted line will be causing your problem
Exit_cboToolGroup_NotInList:
Resume Next
Set db = Nothing
Exit Sub

You could
a)change this one line to
On Error Resume Next

or

b)delete that line
I can't think how/why setting a database object variable to Nothing would cause an error, so it should be safe to delete that line.

That structure may just be Rural Guy's standard practice, and I wouldn't have questioned it if it were say...

Exit_cboToolGroup_NotInList:
   On Error Resume Next
   MyRecordset.close
   set MyRecordset = nothing
   exit sub
(etc)

because in this kind of situation, MyRecordset may or may not be open, and you want to close it if it is, but don't care to see any error messages about trying to close a recordset that is not open, if that happens to be the case. i.e. 'just get on with it'. Be careful, however, to use On Error Resume Next judiciously.

Regards

John

 

[fraser_lindsay]

John,

Your right, I did appreciate a quick answer. Very much so, and it is 'he', except on weekends or special occasions.

Only kidding, ok I changed that line to:

On Error Resume Next

As you requested. I also tried taking it right out but either way I now get know get - Error no 3022:

Description: The changes to the table you requested were not successful because they would create duplicate values in the index, prinary key blah blah. I'm sure you know the rest.

So I'm guessing it is something to do with the way I have asked it to input the data as the new data I used i.e. new tool group isn't a repeat and the ID should be an autonumber.

Do you have any suggestions?

Thanks very much for you help so far.

 

[fraser_lindsay]

Me again. I just worked out what it is - it's the numerical value at the end of the line:

MySQL = "Insert Into tblToolGroup(Tool_Group, ID) " & _
"Values(""" & NewData & """,66)"

I didn't realise the number was the actual value this line added to my ID (autoinumber) line.

As you can see, I really am winging it and generally playing it by ear here. How do I get my code to add an autonumber to a new entry to avoid duplicating any values and getting that error message?

 

[RuralGuy]

Hi Fraser,
If [ID] is an Autonumber field then leave it alone. Access will fill it in when you add the other fields. As in:

MySQL = "Insert Into tblToolGroup(Tool_Group) " & _
"Values(""" & NewData & """)"

I'm glad john471 was able to chip in and move you along further. The suggestion was spot on and a boo boo on my part.

 

[fraser_lindsay]

I'm on a roll now. Everything is working as I want, thanks to you all for your help. Much appreciated.

Time to figure out more advanced reporting. I'm sure I'll be asking you other questions shortly.