By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,044 Members | 1,153 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,044 IT Pros & Developers. It's quick & easy.

figuring out the me statement and columns

P: 11
I have the following vba code It is suppose to go in and search by the appointment field and it fills in others field based on the appointment number selected. This is two fold.

1) I need to add another field but I cannot figure out the columns and how they are associated.

I added txt.door but it is not populating.

2) This has a subform associated with it that I no longer want I just want the information from that subform (which in the same table) to update the table based on my updates. Every time a record is updated it creates a new record but I have it set to edits.

I am revising this database from a person that left the company, so I do not know how to edit her code.

Here is the entire code.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Combo42_Change()
  4.  
  5. End Sub
  6.  
  7. Private Sub cbdAppointment_Change()
  8. Me.txtApptDate = Me.cbdAppointment.Column(2)
  9. Me.txtApptTime = Me.cbdAppointment.Column(3)
  10. Me.txtCID = Me.cbdAppointment.Column(4)
  11. Me.txtLoad = Me.cbdAppointment.Column(5)
  12. Me.txtTrailer = Me.cbdAppointment.Column(6)
  13. Me.txtCarrierName = Me.cbdAppointment.Column(7)
  14. Me.txtShiptoName = Me.cbdAppointment.Column(8)
  15. Me.txtDestinationCity = Me.cbdAppointment.Column(9)
  16. Me.txtTotalCtns = Me.cbdAppointment.Column(10)
  17. Me.bxFloorloadby = Me.cbdAppointment.Column(11)
  18. Me.bxDestination = Me.cbdAppointment.Column(12)
  19. Me.bxP_O = Me.cbdAppointment.Column(13)
  20. Me.bxPalletize = Me.cbdAppointment.Column(14)
  21. Me.bxApplyPros = Me.cbdAppointment.Column(15)
  22. Me.bxPackingListtoPallet = Me.cbdAppointment.Column(16)
  23. Me.bxPackingListtoTruckWall = Me.cbdAppointment.Column(17)
  24. Me.bxPackingListtoLastCarton = Me.cbdAppointment.Column(18)
  25. Me.bxAdditionalInstructions = Me.cbdAppointment.Column(19)
  26. Me.txtDoor = Me.cbdAppointment.Column(20)
  27. Me.bxPreload = Me.cbdAppointment.Column(21)
  28.  
  29. End Sub
  30.  
  31. Private Sub Combo46_Change()
  32.  
  33. End Sub
  34.  
  35. Private Sub Form_AfterUpdate()
  36.  
  37. End Sub
  38.  
  39. Private Sub Form_BeforeUpdate(Cancel As Integer)
  40.  
  41. End Sub
  42.  
  43. Private Sub Form_Load()
  44. DoCmd.GoToRecord , "", acNewRec
  45. End Sub
  46.  
  47. Private Sub Print_LoadSheet_Click()
  48.  
  49. End Sub
Feb 26 '15 #1

✓ answered by jforbes

From looking at the code it appears that the Form has a bunch of unbound controls on it that are populated when an appointment is selected in the cbdAppointment ComboBox.

First, an unbound control is a control put on a Form that is not bound to a field in the table. It won't display anything unless the user types it in or it is set by code. Also, any edits to the field are lost when the Form closes unless there is some code written to specifically save the contents of the field off to the database.

So how are these unbound fields showing me the appointment information when they aren't bound to fields in the database? Well, the information is being pulled from the database when the list of appointments is being generated for the cbdAppointment ComboBox. The appointment information just sits there until the user selects an appointment, then the code in cbdAppointment_Change() shows it in the unbound fields so the user can see it.

You are almost there in displaying additional elements to the user. The one last thing is to update the cbdAppointment's .RowSource property to include the new fields that you want to display. Select the cbdAppointment ComboBox in Design mode of the Form and click the triple dots button on the .RowSource Property. You should be taken to the Query By Example editor where you can add your new columns to the Combobox. Just make sure the columns line up with the columns used in the code for cbdAppointment_Change

Share this Question
Share on Google+
8 Replies


jforbes
Expert 100+
P: 1,107
From looking at the code it appears that the Form has a bunch of unbound controls on it that are populated when an appointment is selected in the cbdAppointment ComboBox.

First, an unbound control is a control put on a Form that is not bound to a field in the table. It won't display anything unless the user types it in or it is set by code. Also, any edits to the field are lost when the Form closes unless there is some code written to specifically save the contents of the field off to the database.

So how are these unbound fields showing me the appointment information when they aren't bound to fields in the database? Well, the information is being pulled from the database when the list of appointments is being generated for the cbdAppointment ComboBox. The appointment information just sits there until the user selects an appointment, then the code in cbdAppointment_Change() shows it in the unbound fields so the user can see it.

You are almost there in displaying additional elements to the user. The one last thing is to update the cbdAppointment's .RowSource property to include the new fields that you want to display. Select the cbdAppointment ComboBox in Design mode of the Form and click the triple dots button on the .RowSource Property. You should be taken to the Query By Example editor where you can add your new columns to the Combobox. Just make sure the columns line up with the columns used in the code for cbdAppointment_Change
Feb 26 '15 #2

P: 11
Thank you so very much for your response. I did add the new field to the query table but it is still not displaying when I select the appointment number. Everything else does display and I verified there is data in the field.
Feb 26 '15 #3

jforbes
Expert 100+
P: 1,107
What is the cbdAppointment.RowSource Property? It should be something like:
Expand|Select|Wrap|Line Numbers
  1. SELECT ApptName, ApptDate, ApptTime... FROM SomeTableName
If you can copy and paste it here, it would do a lot to help us help you.
Feb 26 '15 #4

P: 11
SELECT T_PKMS.ID, T_PKMS.Appointment, T_PKMS.[Appt Date], T_PKMS.[Appt Time], T_PKMS.CID, T_PKMS.Load, T_PKMS.Trailer, T_PKMS.[Carrier Name], T_PKMS.[Ship to Name], T_PKMS.[Total Pallets], T_PKMS.[Destination City], T_PKMS.[Total Ctns], T_PKMS.[Floorload by], T_PKMS.Destination, T_PKMS.[P O], T_PKMS.Palletize, T_PKMS.[Apply Pros], T_PKMS.[Packing List to Pallet], T_PKMS.[Packing List to Truck Wall], T_PKMS.[Packing List to Last Carton], T_PKMS.[Additional Instructions], T_PKMS.[Planned Door], T_PKMS.Preload FROM T_PKMS ORDER BY T_PKMS.Appointment;

I am not sure if I read some where that it could not be more than 20 field. If so, that could be the problem that Planned Door is 21.

Thanks so very much for your assistance
Feb 27 '15 #5

jforbes
Expert 100+
P: 1,107
The 20 column limit sounded plausible, so I tested it out, and found that the 20 column limit is only for using the Wizard to create the ComboBox.

I'm pretty sure the solution to your problem it to change the .ColumnCount Property of your ComboBox to 22. It looks like a Null value is always returned if the column requested is greater than this property.

Most likely, the columns are never even retrieved from the database.
Feb 27 '15 #6

P: 11
Thanks I did get the door to display I moved it to column 7. Now I would like to not have it create a new record, but just update the existing record.

Thanks Again. From the code mention what do I have to remove to make it update without creating a new records.

I do truly appreciate your assistance jforbes.
Feb 27 '15 #7

P: 11
This corrected my column issue. Thank you so very much. How do I edit in reverse. What I add the form going back to the table?
Feb 28 '15 #8

jforbes
Expert 100+
P: 1,107
That's a completely new problem and on these Forums there is one problem/question per thread. We'll get straightened out if you start a new thread and describe in detail what you are experiencing and what you think should happen.
Feb 28 '15 #9

Post your reply

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