figuring out the me statement and columns

[Darcjohnson01]

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.

Option Compare Database

Private Sub Combo42_Change()

End Sub

Private Sub cbdAppointment_Change()
Me.txtApptDate = Me.cbdAppointment.Column(2)
Me.txtApptTime = Me.cbdAppointment.Column(3)
Me.txtCID = Me.cbdAppointment.Column(4)
Me.txtLoad = Me.cbdAppointment.Column(5)
Me.txtTrailer = Me.cbdAppointment.Column(6)
Me.txtCarrierName = Me.cbdAppointment.Column(7)
Me.txtShiptoName = Me.cbdAppointment.Column(8)
Me.txtDestinationCity = Me.cbdAppointment.Column(9)
Me.txtTotalCtns = Me.cbdAppointment.Column(10)
Me.bxFloorloadby = Me.cbdAppointment.Column(11)
Me.bxDestination = Me.cbdAppointment.Column(12)
Me.bxP_O = Me.cbdAppointment.Column(13)
Me.bxPalletize = Me.cbdAppointment.Column(14)
Me.bxApplyPros = Me.cbdAppointment.Column(15)
Me.bxPackingListtoPallet = Me.cbdAppointment.Column(16)
Me.bxPackingListtoTruckWall = Me.cbdAppointment.Column(17)
Me.bxPackingListtoLastCarton = Me.cbdAppointment.Column(18)
Me.bxAdditionalInstructions = Me.cbdAppointment.Column(19)
Me.txtDoor = Me.cbdAppointment.Column(20)
Me.bxPreload = Me.cbdAppointment.Column(21)

End Sub

Private Sub Combo46_Change()

End Sub

Private Sub Form_AfterUpdate()

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
End Sub

Private Sub Form_Load()
DoCmd.GoToRecord , "", acNewRec
End Sub

Private Sub Print_LoadSheet_Click()

End Sub

[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

[Darcjohnson01]

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.

[jforbes]

What is the cbdAppointment. RowSource Property? It should be something like:

SELECT ApptName, ApptDate, ApptTime... FROM SomeTableName

If you can copy and paste it here, it would do a lot to help us help you.

[Darcjohnson01]

SELECT T_PKMS.ID, T_PKMS.Appointm ent, 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.Destinat ion, T_PKMS.[P O], T_PKMS.Palletiz e, 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.Appointm ent;

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

[jforbes]

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.

[Darcjohnson01]

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.

[Darcjohnson01]

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?

[jforbes]

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.