Adding new item into current subform record (1 Viewer)

jaryszek

Registered User.
Local time
Today, 02:37
Joined
Aug 25, 2016
Messages
756
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

  • Screenshot_4.png
    Screenshot_4.png
    50.6 KB · Views: 527
  • SampleDatabase.accdb
    488 KB · Views: 529

theDBguy

I’m here to help
Staff member
Local time
Today, 02:37
Joined
Oct 29, 2018
Messages
21,357
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
Today, 20:37
Joined
Jul 4, 2013
Messages
2,770
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

..forever waiting... waiting for jellybean!
Local time
Today, 17:37
Joined
May 7, 2009
Messages
19,169
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

  • SampleDatabase (1).zip
    38.6 KB · Views: 509

jaryszek

Registered User.
Local time
Today, 02:37
Joined
Aug 25, 2016
Messages
756
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

..forever waiting... waiting for jellybean!
Local time
Today, 17:37
Joined
May 7, 2009
Messages
19,169
You are correct, and the query gets it data from Msys table, so it is readily available.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:37
Joined
May 7, 2009
Messages
19,169
you are welcome!
 

jaryszek

Registered User.
Local time
Today, 02:37
Joined
Aug 25, 2016
Messages
756
Ok,

and how this is working?

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

why Msysobjects has only 10 numbers?

Jacek
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:37
Joined
May 7, 2009
Messages
19,169
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

Top Bottom