473,320 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Passing a new CustID back to a ComboBox and Requery/Refresh the form.

SueHopson
47 32bit
Ok, sorry folks, but I simply can't wrap my head around this no matter how much I research

I have a ComboBox cmb_CxLookup whose row source is
Expand|Select|Wrap|Line Numbers
  1. SELECT qry_CustMAIN.CustID, qry_CustMAIN.CustName, * FROM qry_CustMAIN ORDER BY qry_CustMAIN.CustName;
The Bound column is 1 CustID, which is hidden from the user

My AfterUpdate code is
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmb_CxLookup_AfterUpdate()
  2.     DoCmd.SearchForRecord acDataForm, "frmMain", acFirst, "[CustID] = " & str(Nz([Screen].[ActiveControl], 0))
  3.     sbfmQuotesRefresh
  4. End Sub
This works perfectly on my main form frmMAIN (record Source is qry_CustMAIN) which has 2 subforms that display with the correct customer information once the selection has been made from ComboBox cmb_CxLookup
- sbfm_CustDetails
- lstQuoteSummary

To keep users from accidentally deleting customer information on the frmMAIN, I have locked sbfm_CustDetails and users must open frmCustMAIN (record Source is also qry_CustMAIN) from one of 2 buttons New Customer or Edit Customer. Edit works fine, but when I add a new customer (new CustID created) and close the form I'm stalling out.

My OnClose event is below
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2. 'Requery the Main Customer List
  3.     DoCmd.OpenQuery "qry_CustMAIN"
  4.     DoCmd.Requery 'the record shows at this stage
  5.     DoCmd.Close
  6. 'Requery Customer List and set value to new record
  7.     Forms![frmMAIN].Requery
  8.     Forms![frmMAIN].cmb_CxLookup.Requery
  9.     Forms![frmMAIN].cmb_CxLookup.Value = Me.CustID
  10.     Forms![frmMAIN].cmb_CxLookup.setFocus
  11. End Sub
When the form closes and the focus returns to frmMAIN the customer is indeed selected in the cmb_CxLookup as expected, but I still need to click to get the AfterUpdate code to run for the new company to display in the subforms. Is there any way to automate this?

Any help, as always is greatly appreciated.
Feb 15 '23 #1

✓ answered by NeoPa

Hi Sue.

I've not got a system to check against at the moment, but I seem to recall that Events don't get triggered in Access Forms if they're set in code. I may be wrong, but it's ringing a bell somewhere.

Anyway, to get around it you can call the Event procedure directly from your code.

Actually, there's a more appropriate way to handle this. Write a separate procedure to do the work required after the update happens and call this same procedure from both the Event procedure and the code where you set the value directly.

It's wise to avoid calling Event procedures directly in code as they are predefined to work in reaction to the specific event firing - so having code running in a separate context, that is expected to run in a specific context, is likely to lead to confusion. If not for you then at least for someone who may follow you. Not a good approach.

4 18190
NeoPa
32,556 Expert Mod 16PB
Hi Sue.

I've not got a system to check against at the moment, but I seem to recall that Events don't get triggered in Access Forms if they're set in code. I may be wrong, but it's ringing a bell somewhere.

Anyway, to get around it you can call the Event procedure directly from your code.

Actually, there's a more appropriate way to handle this. Write a separate procedure to do the work required after the update happens and call this same procedure from both the Event procedure and the code where you set the value directly.

It's wise to avoid calling Event procedures directly in code as they are predefined to work in reaction to the specific event firing - so having code running in a separate context, that is expected to run in a specific context, is likely to lead to confusion. If not for you then at least for someone who may follow you. Not a good approach.
Feb 16 '23 #2
SueHopson
47 32bit
Hey Neo,

After reading your comments, I decided a different approach would be better and instead called the sbfm edits from within the subform itself. I need the subform locked until the user makes a conscious decision to edit them because well, my users aren't the most attentive...

All 3 subforms begin with sbfm_Cx and I have all 3 locked on open. For space they are set on a Tab Control
tab_Customers
stb_CxInfo
stb_CxContacts
stb_CxNotes

The code below works great when I am editing within the subform itself, but I have a new problem when accessing the sbfm from the frmMain

Expand|Select|Wrap|Line Numbers
  1. Private Sub CxSbfmUnlock()
  2. Dim ctrl As Control
  3. Dim CxEditClr As Long
  4.     CxEditClr = RGB(230, 185, 184)
  5.     Me.Detail.BackColor = CxEditClr
  6.     Me.AllowEdits = True
  7.     Me.AllowDeletions = True
  8.     Me.AllowAdditions = True
  9. End Sub
  10.  
  11. Private Sub CxSbfmLock()
  12. Dim ctrl As Control
  13. Dim CxRtnClr As Long
  14.     CxRtnClr = RGB(242, 220, 219)
  15.     Me.Detail.BackColor = CxRtnClr
  16.     Me.AllowEdits = False
  17.     Me.AllowDeletions = False
  18.     Me.AllowAdditions = False
  19. End Sub
  20.  
  21. Private Sub btn_CxEdit_Click()
  22.     CxSbfmUnlock
  23. End Sub
  24.  
  25. Private Sub btn_CxNew_Click()
  26.     CxSbfmUnlock
  27.     DoCmd.GoToRecord , , acNewRec
  28. End Sub
The Allow functions are failing when I try and access the sbfm from the frmMain (the records stay locked). I know I can manually change the Unlock code to reference each sbfm individually but I run into the same error with the Allow fields failing.

My research leads me to believe that there is something in the relationship of the Controls between the form and subform that I am missing, but my understanding is still marginal at best. I've spent the last 3 days playing with the code below to see if there is a way to unlock all three forms at once on edit/new and have the Allow to function.

Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control
  2. For Each ctl In Me.Controls
As always - all thoughts appreciated.
Mar 10 '23 #3
NeoPa
32,556 Expert Mod 16PB
Hi Sue.

It's late for me here so I'll just post a link (Referring to Items on a Sub-Form) as I suspect you may be confusing the SubForm control with the Form that may, or may not, be held within that control.

Let me know if that unlocks your thinking or if we need to delve further.
Mar 12 '23 #4
SueHopson
47 32bit
Got it! Thanks for the Referring to Items on a Sub-Form link.
Love learning with you!
Apr 12 '23 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: aaa | last post by:
Hello to all, In an application under VB6, I need to fill some ComboBoxes with data. However, I don't know in advance how many ComboBoxes I will need. I could of course create many (maybe 15 or...
1
by: Bill | last post by:
I have five users using the same forms to enter and retrieve data from an Access database held on a server but the only way each user can see any changes to records is by closing and reopening the...
2
by: Stephen Costanzo | last post by:
My goal is to open Form2 from Form1 and have Form2 pass back an integer to Form 1. In VB, this is rather simple: Form 1 (vanilla form with a button): Dim WithEvents x As Form2 Dim i As...
3
by: Chris | last post by:
Hi, On my form, when the user hit submit it validates the zip code and if more than one city is returned it pops up a form with the list of cities in a list box. The user then have to select one...
5
by: Ronald S. Cook | last post by:
It's been longer that I remember since writing windows (not web) apps. 1) I want to load a main form 2) User clicks login button which brings up login form (on top of main form) 3) Upon...
6
by: Terrance | last post by:
Hello, everyone I'm new to C# and I was wondering if someone can tell me how do I pass a value from 1 form back to the calling routine. What I mean is in my Parent form I have this code: private...
3
by: Gilberto | last post by:
Hello, I have a form with some buttons that take the user to reports, other forms, etc. On this form the user can see how many products are missing costing and pricing information, through some...
3
by: beemomo | last post by:
I have two tables - 'Client' and 'Company', and two forms, 'Client' and 'Company', the relationship between two table is 1:many where a company can have one or more client but each of the client...
7
by: CF FAN | last post by:
need logic so when the page is refreshed that it returns to the location of the change, not to the top of the page. Currently, when the page is refreshed, it takes you back to the beginning of...
1
by: Martha Amer | last post by:
Hello, I was wondering what would be the most efficient and correct way to have two forms with let's say one text box in each form, when I change the text in the text box in form 1 the text gets...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.