Adding new item into current subform record (1 Viewer)

[jaryszek]

Hi Guys,

i created simple example database to reproduce error with i am facing off.

On my form i have Group: Men and Women and on subform i have Names and NumberOfPets:

The combobox source is from People.NumberOfPets column.

What i want to do is to update current record with the added item to combobox.

I am using code:

Private Sub Combo8_NotInList(NewData As String, Response As Integer)

'MsgBox "ok"

Dim strTmp As String
Dim Result As String
Result = NewData

'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new number?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then

        strTmp = "Update People SET NumberOfPets = " & NewData & _
            " WHERE PeopleID = " & Form_GroupPeopleSubform.PeopleIDFK.Value

'        MinInstanceCmb.Value = 1
        CurrentDb.Execute strTmp, dbFailOnError

        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
      '  DoCmd.GoToRecord , , acNext

       'DoCmd.RunCommand acCmdSaveRecord
'        MinInstanceCmb.Value = Result
        'UPDATE someTable SET someDate = Date() WHERE stuff = 47"

        End If

End Sub

But i am getting error:

How can i solve this?
I suppose that i am editing table right now so i have to leave current row, update table, choose specific value in combobox and return to the specific row.
Am i am thinking properly?

Please help,
Jacek

 

[theDBguy]

Hi. You may not be using the NotInList event correctly in this situation. Looking at your code, you are "updating" the table with the new value. But normally, the NotInList event is used for "adding" new values into a table.

 

[Cronk]

The issue is that both the control and row source of the combo is based on the People table. The row source should be either hard coded into the combo or based on a separate lookup table.

Change the row source to 1;2;3;4;5

 

[arnelgp]

here is your database.
the "number of pet" is derived from a query (qry0To99).
I think the numbers (upto 99) is sufficient.
check the subform's Link Master Fields.
I change it to point to the Combobox (not GroupID).

 

[jaryszek]

thank you Guys!!!

arnelgp awesome!

how this is working?:

Can you explain?

SELECT ([qry0To9].[NUMS]*10)+[qry0To9_1].[NUMS] AS Expr1
FROM qry0To9, qry0To9 AS qry0To9_1
ORDER BY ([qry0To9].[NUMS]*10)+[qry0To9_1].[NUMS];

So generally you just as row source added numbers from 0 to 99 and the field is connected into NumberOfPets?

Jacek

 

[arnelgp]

You are correct, and the query gets it data from Msys table, so it is readily available.

 

[jaryszek]

thank you very much!

 

[arnelgp]

you are welcome!

 

[jaryszek]

Ok,

and how this is working?

SELECT DISTINCT Abs([ID] Mod 10) AS NUMS
FROM MSYSOBJECTS;

why Msysobjects has only 10 numbers?

Jacek

 

[arnelgp]

MsysObjects has many ID's, some positive and negatives.
in my db I have 1,183.
use Abs() to convert the negative to positives.

(id Mod 10) will result to 0,1... up to 9
these numbers will repeat so we add Distinct keyword.