Adding new item into current subform record (1 Viewer)

jaryszek

Registered User.
Local time
Today, 15:53
Joined
Aug 25, 2016
Messages
662
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:

1590760394013.png

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:

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:
1590760517446.png

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
 

Attachments

theDBguy

I’m here to help
Local time
Today, 15:53
Joined
Oct 29, 2018
Messages
10,765
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

Registered User.
Local time
Tomorrow, 08:53
Joined
Jul 4, 2013
Messages
2,381
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

error reading drive A:
Local time
Tomorrow, 06:53
Joined
May 7, 2009
Messages
9,600
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).
 

Attachments

jaryszek

Registered User.
Local time
Today, 15:53
Joined
Aug 25, 2016
Messages
662
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

error reading drive A:
Local time
Tomorrow, 06:53
Joined
May 7, 2009
Messages
9,600
You are correct, and the query gets it data from Msys table, so it is readily available.
 

jaryszek

Registered User.
Local time
Today, 15:53
Joined
Aug 25, 2016
Messages
662
Ok,

and how this is working?

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

why Msysobjects has only 10 numbers?

Jacek
 

arnelgp

error reading drive A:
Local time
Tomorrow, 06:53
Joined
May 7, 2009
Messages
9,600
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.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom