Not in List

[scouser]

I have a form 'frmOrders' with a sub form 'frmOrderDetails'. On the sub form I have a combo 'cboMake'. If the user enters a Vehicle Make that does not exist the following code is run:

Private Sub cboMakeID_NotInList(NewData As String, Response As Integer)
DoCmd.OpenForm "frmMakes", acNormal, , , acFormEdit, acWindowNormal
DoCmd.GoToRecord , , acNewRec
DoCmd.Close acForm, Me.Name
End Sub

So now frmMakes is open. This allows the user to enter the vehicle Make that was not in the list. The user then clicks a command button to return to the Orders Form. The On_Click event of this command is as follows:

Private Sub cmdReturnToOrderForm_Click()
On Error GoTo Err_cmdReturnToOrderForm_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmOrders"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, Me.Name
    
Exit_cmdReturnToOrderForm_Click:
    Exit Sub

Err_cmdReturnToOrderForm_Click:
    MsgBox Err.Description
    Resume Exit_cmdReturnToOrderForm_Click
    
End Sub

So now the user is returned to the Orders Form. However the Vehicle Make he just entered does not appear in the list unless he naviagtes away from the record and back again?

Any ideas on how after closing the frmMakes the newly entered record appears in the cboMakeID?

Thanks in advance,
Phil.

 

[Newman]

Haven't tested it, but adding this:

DoCmd.Requery (Form_frmOrderDetails.cboMakes)

in the «cmdReturnToOrderForm_Click()» event should do the trick.

 

[scouser]

Thanks

Thanks for replying 'Newman'. I did as you suggested:

DoCmd.Requery (Form_frmOrderDetails.MakesID)

When the Order form reloads I get an error:

There is no Field named '0' in the current record

The value '0' changs with each error.
Any ideas?
Cheers,
Phil.

 

[Newman]

Sorry, I forgot the quotes...

DoCmd.Requery ("Form_frmOrderDetails.cboMakes")

 

[scouser]

Hello Again

Hi Newman. I tried that an got error:

There is no field name 'Form_frmOrderDetails.MakeID' in the current record

Field MakeID is a combo if that makes any difference?

Cheers,
Phil.

 

[IfButOnly]

Phil,
Try something like this -

In the cboMakeID_NotInList module, use:-

DoCmd.OpenForm "frmMakes", acNormal,,,acFormAdd,,NewData
Response = acDataErrAdded
(which opens frmMakes in Add mode and passes the combo string across in OpenArgs, and will requery the combo on return.)

However, Unless you are entering details on frmMakes form that are not available from frmOrders, I would create the new make without user intervention by using an SQL Insert or DAO AddNew event. Just a thought.

Peter

 

[scouser]

Hi Peter, thanks for replying.

In the cboMakeID_NotInList module, use:-

DoCmd.OpenForm "frmMakes", acNormal,,,acFormAdd,,NewData
Response = acDataErrAdded
(which opens frmMakes in Add mode and passes the combo string across in OpenArgs, and will requery the combo on return.)

Unfortunately that did not do the trick. I have attached a screen dump of the form so you can visualise what is happening.

The view you see is after the Not In List event has been triggered. I entered the Make 'Daewoo' in the Make Form and returned to the order from. As you can see, on returning to form Orders the Make 'Daewoo' is still not available for selection? This then re-triggers the not in list event (viscous circle!!).

As for:

However, Unless you are entering details on frmMakes form that are not available from frmOrders, I would create the new make without user intervention by using an SQL Insert or DAO AddNew event. Just a thought.

Beyond the scope of my tiny brain!! Can you elaborate?
Many Thanks,
Phil.

 

[IfButOnly]

Phil, you can find many examples on the site by using the Search facility - I have listed one below, couresty of Allen Browne, which has some documentation.

-----------------------------------------------------------------------

MS-Access Tips for Serious Users
Provided by Allen Browne, allen@allenbrowne.com


--------------------------------------------------------------------------

NotInList: Adding values to lookup tables
Every database application uses combos for selecting a value from a lookup table. By setting the combo's LimitToList property to Yes, you can use the NotInList event to append a new entry to the lookup table.

If several fields are to be entered (e.g. adding a new client), open a data entry form in dialog mode:

DoCmd.OpenForm "MyForm",,,, acAdd, acDialog
Dialog mode pauses your code until the entry is complete. You can then use acDataErrAdded to cause Access to find the new value.

In other situations only a single field is needed, such as a category, or a title like Mr/Ms/Dr/... Opening a form is unnecessary, as the user has already typed the new value. The function below verifies the entry and appends it to the lookup table.

This function identifies the lookup table from the combo's RowSource property. It assumes the field name in the lookup table is the same as the combo's ControlSource, i.e. the primary key name and foreign key name must be the same.

Follow these Steps:

Paste the function below into a general module. Save the module.
Verify the combo's LimitToList property is Yes.
In the NotInList property of your combo, choose [Event Procedure].
Click the "..." button so Access opens the code window.
Between Sub ... and End Sub, enter:
Response = Append2Table(Me![MyCombo], NewData)
replacing MyCombo with the name of your combo box.
Repeat steps 2 - 4 for other combos.
This function will not work with Access 2 without modification.


--------------------------------------------------------------------------

Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
On Error GoTo Err_Append2Table
' Purpose: Append NotInList value to combo's recordset.
' Assumes: ControlSource of combo has the same name as the foreign key field.
' Return: acDataErrAdded if added, else acDataErrContinue
' Usage: Add this line to the combo's NotInList event procedure:
' Response = Append2Table(Me.MyCombo, NewData)
Dim rst As DAO.Recordset
Dim sMsg As String
Dim vField As Variant ' Name of the field to append to.

Append2Table = acDataErrContinue
vField = cbo.ControlSource
If Not (IsNull(vField) Or IsNull(NewData)) Then
sMsg = "Do you wish to add the entry " & NewData & " for " & cbo.Name & "?"
If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
rst.AddNew
rst(vField) = NewData
rst.Update
rst.Close
Append2Table = acDataErrAdded
End If
End If

Exit_Append2Table:
Set rst = Nothing
Exit Function

Err_Append2Table:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbInformation, "Append2Table()"
Resume Exit_Append2Table
End Function
-------------------------------------------

Peter

 

[scouser]

Wow

Peter what a comprehensive answer, many thanks. I followed all your steps and received error:

Error 3421: Data type conversion

Am I a lost cause?
This is the last major hurdle I have to overcome (this and data import!).
I have stripped the database to post.
Many Thanks,
Phil.

 

[IfButOnly]

Phil, you are almost there..

The line - rst(vField) = NewData, should be - rst("Make") = NewData.

vField actually picks up the name from the Combo boxes Control Source and uses it - but in this case it is not quite what we are after.

Anyhow the change above should work for you.

Peter

 

[ansentry]

Have a look at the attached database, you can now add a Make and it will now appear immediately on you list.

I have not added it as a function just code that runs "on not in list"

 

[scouser]

Superb

Guys, many, many thanks. What a truly great forum this is!!
Peter I do not know what I am doing wrong with your function , but still I receive an error: Now I get:

3265: Item not found in this collection

Your function reads as:

Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
On Error GoTo Err_Append2Table
' Purpose: Append NotInList value to combo's recordset.
' Assumes: ControlSource of combo has the same name as the foreign key field.
' Return: acDataErrAdded if added, else acDataErrContinue
' Usage: Add this line to the combo's NotInList event procedure:
' Response = Append2Table(Me.MyCombo, NewData)
Dim rst As DAO.Recordset
Dim sMsg As String
Dim vField As Variant ' Name of the field to append to.

Append2Table = acDataErrContinue
vField = cbo.ControlSource
If Not (IsNull(Make) Or IsNull(NewData)) Then
sMsg = "Do you wish to add the entry " & NewData & " for " & cbo.Name & "?"
If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
rst.AddNew
rst(Make) = NewData
rst.Update
rst.Close
Append2Table = acDataErrAdded
End If
End If

Exit_Append2Table:
Set rst = Nothing
Exit Function

Err_Append2Table:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbInformation, "Append2Table()"
Resume Exit_Append2Table
End Function

The Not in List event is:

Private Sub cboMakeID_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![cboMakeID], NewData)
End Sub

It must be me!!
Many Thanks,
Phil.

 

[scouser]

Me again!

'Ansentry'. Works a treat, many thanks........................

 

[scouser]

Not in List on cboModelID

Back again! John A I modified your code for the second combo: cboModelID as if the user enters a new Make then they MUST be able to enter a Model for that Make. Code as follows:

Private Sub cboModelID_NotInList(NewData As String, Response As Integer)
Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

    strMsg = "'" & NewData & "' is not an available Vehicle Model " & vbCrLf & vbCrLf
    strMsg = strMsg & "Do you want to add the new Model to the current Database?"
    strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add or No to re-type it."
    
    If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new model?") = vbNo Then
        Response = acDataErrContinue
    Else
        Set DB = CurrentDb
        Set rs = DB.OpenRecordset("tblModels", dbOpenDynaset)
        On Error Resume Next
        rs.AddNew
            rs!Model = NewData
        rs.Update
        
        If Err Then
            MsgBox "An error occurred. Please try again."
            Response = acDataErrContinue
        Else
            Response = acDataErrAdded
        
    End If
    
rs.Close
Set rs = Nothing
Set DB = Nothing
End If
End Sub

I get default Access error: 'The text you entered isn't an item in the list'

I can not see why that error is generated, can you?
Once again, many thanks,
Phil.

 

[ansentry]

Your are trying to add a model when you have to add model and makeID.

Just a passing question, did my code work for the Make?

 

[scouser]

'Ansentry'. Works a treat, many thanks........................

Yes John, the above was regards the Make.

You are right I do need to pass MakeId and Model. How the hell do I do that and get it all to tie up?

My brain is scrambled as I have been copying Vehicles Makes and Models from the Autotrader web site!! 900+..........dizzy!!

 

[IfButOnly]

John,

Add the following line to your module...

rs.AddNew
rs!MakeID = Me.cboMakeID
rs!Model = NewData
rs.Update

Assuming the names are correct, this will add the Make key to your Model table.

Are you using a cascading combo box for your models to limit the selection to models for the make selected??

Peter

 

[ansentry]

Phil and Peter,

I just tried Peters code and it works fine.


Good luck with the rest of it Phil.

 

[scouser]

Thanks

Thanks guys. I will have a play with that now and see how I get on!!

 

[scouser]

Superb

Peter, John.......Fantastic, really appreciate your efforts.....It works a treat.
Many Thanks,
Phil.