MS ACCESS Autofilled Text box to Table

Hi Dustin,

I'd have to see your code but do you include explicit statement to capture the text of the autofilled box?  It may be as simple as you forgot the name of the box (hopefully!)

Also, can you "step in to" the code on submission and see what the values are as you progress?

Here's a sample of what I've done in the past with a training database:

 

Private Sub TrainingSubmitButton_Click()

On Error GoTo UhOh

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim UID As String
Dim TID As String
Dim Training As String
Dim DC As Date
Dim Notes As String
Dim rev As String
Dim EmpName As String
Dim DocNum As String
Dim Trainer As String

    UID = UIDSubmit
    TID = TrainingSubmit
    Training = TrainingTitleSubmit
    DC = DateSubmit
    Notes = NotesSubmit
    rev = revSubmit
    EmpName = Me.EmployeeName
    DocNum = Me.document
    Trainer = Me.Trainer

DoCmd.OpenQuery ("set most recent training flags in tracker - update query")

    Set db = CurrentDb
    Set rst = db.OpenRecordset("Training Tracker", dbOpenDynaset)

    rst.AddNew
    rst![University ID] = UID
    rst![Employee Name] = EmpName
    rst![Training ID] = TID
    rst![Training] = Training
    rst![Date Completed] = DC
    rst![Notes] = Notes
    rst![Revision] = rev
    rst![Doc Number] = DocNum
    rst![Trained By] = Trainer
    rst.Update
    rst.Close

    db.Close
    Me!TrainingSubmit = ""
    Me!DateSubmit = ""
    Me!NotesSubmit = "N/A"

DoCmd.OpenQuery ("Trainings not in compliance - update query")
[Training Compliance].Form.Requery
[Training Needed].Form.Requery
Form.Refresh
Exit Sub

UhOh:
MsgBox ("Please ensure that you have selected a training and a date before pressing submit")
Exit Sub

End Sub

Select allOpen in new window

Thanks for the quick response. I have little, to no experience in vba. All I have done is typed the below expression in the control source for the text box. The text box is being autofilled based on the selection the user selects in the Associate field. The asociate field, cboAssocaite, is a combo box that a query is bound to, that reports the associate and their corresponding team lead. so I can simply autofill the teamlead based on the second column. My problem is that the autofill does not link back to the table.

=[cboAssociate].[column](1)

Nope, it doesn't ;)  Sorry, it doesn't work that way.  What I've done with this in the past is used labels instead of text boxes with NO intention of storing the information.  Maybe I can persuade you to not store this extra info?

If it's already look-upable then you really don't have a reason to store it twice (data redundancy).  It won't appear in the table but when viewing the form it will be visible.  The only reason to store data is because you need a static record that will definiately vary over time.  

VBa is a pretty nice skill to have with regard to Microsoft Access.  I had to train myself in it using web tutorials and examples.  It paid off in spades.  I'm thinking you need a submit button that will mimic somewhat that's in the code snippet above to do what you're asking; that will look at the text in the box and apply the values in to the table.  Still, since it is queryable, work with queries and reports instead of tables!  Then you don't have to store it directly in the table.  

The reason I want to store the date, the Team Lead, in this example is because our associates get assigned to different team leads quite often. So if I update the associate table with the new team lead, then the historic data would be overwritten since it is not store anywhere.

I agree that I need to learn vb. I taught myself sql and ssrs, vb is next on the list!

Ok.  I'm trying to find the path of least resistance for you here....

How does your form make the connection between associates and the team lead now?  Also, how many team leads do you have?

The autofill is what's getting in your way here since that info doesn't post.  If you don't have too many team leads, can you populate the contents of the combo box with the query and force the user to make a selection from the list?  

It may help to post an image of the form if you can.

Set the control of the calculated field control to a name in your table.  It is saying:  
ThisField (field name) is =[cboAssociate].[column](1)
If you don't have a field to store it in the table, add a field to the table before you rename the calculated field control.

This work could be done in Visual Basic, but it is not necessary for this call.

Hope you're right DoDahD.  If so, I completely appologize for steering you in the wrong direction.  This is a hell of a lot easier than a VB code up.

Why have a separate text box? Why can you not simply have the combobox in place with it's control source equal to the name of the field you want the data saved in and the row source can stay as it is, being the list of associates or whatever it is. When the record is saved, the field will take the value of the combobox. I see no reason to have a separate textbox for this purpose

The question is how to save the data and not whether to save the data.  
For example, as you move to Access Web Services, you may find the calculated value here easier to work with in a SharePoint list.  Then, of course, you have Calculated fields availabe in AC2010 to address this as an option.

So I created a combobox with the control source as the field I would like the team lead to be saved. The rowsource = =[cboAssociate].[column](1), which is dependent on which associate is selected from the associate combobox. When I select the associate, no team lead is listed in the drop down. When I click on the drop down for the team lead field, I get "the record source =[cboAssociate].[column](1) does not exist.

By dumb luck, I figured this out. I had to create two macros to capture the team lead and update it back to the table.


Private Sub cboAssociate_AfterUpdate()

Me.TEAM_LEAD = Me.cboAssociate.Column(1)

End Sub

Private Sub txtTeamLead_AfterUpdate()
Call cboAssociate_AfterUpdate
End Sub

I can see what cboAssociate is doing, that is your combox with rowsource as your list of associates (is the team leader chosen from a list of these associates?) but I don't see what TEAM_LEAD is or why you still have txtTeamLead in place

If you remove txtTeamLead and set the control source of cboAssociate to the team lead field in the table you will find that the selected item in the combox automatically update the field when the record is saved

I am creating a form for Team Leads to enter quality violations for their Associates. Team_Lead is the name of the field in the talbe. So when a user selects an associate from the dropdown on the form, the correct Team Lead is automatically autofilled and updated to the quality violations table.

Nice work daz that is exactly what I was looking for. Now that I understand this I have another situation that is quit similar.

I have one table titled qv_categories,  with fields code, section, category, subcategory (which can be null). If a user selects a section (either Compliance or Procedural) I would like the corresponding categories available from a drop down. Then when that category is selected to list the available subcategories. Attached is what the table looks like.  
QV-Categories.xlsx

I've just looked at your spreadsheet and in response to your second question, I reckon this page should answer your question.

If you need a further explination just ask, but I think the page covers the topic pretty well, and almost exactly fits your issue

http://blogs.office.com/b/microsoft-excel/archive/2009/11/24/create-conditional-drop-down-lists.aspx

Daz,

Maybe I didn't explain what I needed exactly. The attached spreadsheet has been imported into access as a table called qv_categories. I created a form that a user must select which section between compliance or procedural, then the next field "category" would be prefilled in a combobox with categories tied to the section chosed. The same would go for the subcategory. All three sections should be recorded on the qv_category table.

Thanks,
Dustin

Hi Dustin,

The effect you are looking for could be quite easily achieved by altering the rowsource property of the comboxes within the after_update event of the previous combox. This could be cascaded to any number of columns and as long as the control source of the comboxes is matching the field names of the qv_category table the records will update appropriately without any further interaction.

As much as I hate to be seen as cheeky, I would appreciate the points for answering your initial question with an answer you seem to be very satisfied with. This is a completely separate question. If you could post it up as a separate question I would be quite happy to write an example for you to use.

Daz

Sure thing! That was my first post, so I was unclear of the protocol! I will post up a new thread as well.

However, I agree with your reply, but how can I limit the section to only one compliance and one procedural option. In the excel spreadsheet there are multiple sections listed as compliance and procedural with different categories.

Thanks again for all your help!