Listbox onClick event to refresh current form (1 Viewer)

A1ex037

Registered User.
Local time
Today, 13:34
Joined
Feb 10, 2018
Messages
50
Hello everyone,
I hope someone will have an answer for the question that is bugging me.

When I open Sales form and go to NewRecord in order to enter new sale, SaleID field (which is also AutoNumber field and a PK for tblSales) is blank. It says (New), and that is OK since nothing is entered still. When I select a customer from a listbox, i have onClick event that correctly enters the CustomerID into tblSales. If I check tblSales, new record is created, also the AutoNumber ID, but the form still holds (New). I have tried anything I can think of, but I cannot get it to show this field (which I need later on the same form to enter purchased products in order to relate SaleID with purchased products). I have tried refreshing the form, various events, but nothing.

In earlier variant of the mdb, ComboBox served as a customer selection, and everything worked nicely (on AfterUpdate event).
Now, the only way I see is that after customer selection, I find the last row and use that number to connect with purchased products. But I cannot understand why AutoNumber field is not shown.

Thanks to all.
 

bob fitz

AWF VIP
Local time
Today, 12:34
Joined
May 23, 2011
Messages
4,717
When I select a customer from a listbox, i have onClick event that correctly enters the CustomerID into tblSales.
If the form is based on the "Sales" table you should be able to to set the "Control Source" property of the list box (I would use a combo box) to CustomerID. No code would then be needed to set the CustomerID.

I have tried anything I can think of, but I cannot get it to show this field (which I need later on the same form to enter purchased products in order to relate SaleID with purchased products).
Sound like you need a main form for each sale to a customer and a sub form based on a SalesDetail table to handle each item sold.

To answer your original question: Have you tried to save the record with something like:
Code:
If Me.Dirty = True Then
   Me.Dirty = False
End If
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:34
Joined
Aug 30, 2003
Messages
36,118
It sounds like you have code running to create a record and have the form bound. It should be one or the other, not both.
 

A1ex037

Registered User.
Local time
Today, 13:34
Joined
Feb 10, 2018
Messages
50
Thank you both for your responses. You were actually both right, and I really appreciate both responses.



Looks like this did the trick. It's placed on frmSales (OnCurrent event).



Code:
If Me.Dirty = True Then    Me.Dirty = False End If


I will test it a bit more now.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:34
Joined
Aug 30, 2003
Messages
36,118
Really? I can't see how the form could ever be dirty in the current event. Maybe I'm having a brain cramp.
 

A1ex037

Registered User.
Local time
Today, 13:34
Joined
Feb 10, 2018
Messages
50
My mistake. It was place in lstCustomers OnClick event.
Thank you for pointing that out.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:34
Joined
May 7, 2009
Messages
19,169
you probably should use the AfterUpdate Event of the list to add the CustomerID to the sales table.

the following code will add the CustomerID (if column 1 on your list is the CustomerID) to New sales record.

then it will go to that Last saved record.
Code:
Private Sub lstCustomers_AfterUpdate()
          With Me.RecordsetClone
                     .AddNew
                     !CustomerID = lstCustomers
                     .Update
                     Me.Bookmark = .LastModified
          End With
End Sub
If the list is Unbound, using Dirty=False to the Form's current event has no effect.
 

Users who are viewing this thread

Top Bottom