populating text boxes from the result of a query

[cooper]

Hi,

I have a subform with many text boxes - these text boxes are supposed to show the different fields in the original table.

What I want to do is - let the user select a date from a combo box, and as the user selects a particular date, the text boxes are all populated. Also, the user should be able to edit these text boxes and save the changes without having to add new record (over writing data).



A new record is only added if a new date is selected. I have a query (mainquery) with the following SQL that shows the values I want, but how do I use the result to populate these text boxes:

SELECT Table2.Date, Table2.Employee_ID, Table2.[Caseflow Shelving Available], Table2.[Caseflow Shelving Actioned], Table2.[High Cube Available], Table2.[High Cube Actioned], Table2.[Prewab Report Available], Table2.[Prewab Report Actioned], Table2.[Run to Zero/Frozen Availabl], Table2.[Run to Zero/Frozen Actioned]
FROM TblEmployees LEFT JOIN Table2 ON TblEmployees.Employee_ID = Table2.Employee_ID;


I would really appreciate if someone can help me out here -

I can post the sample database if someone would like to have a look.

Thanks!!

[June7]

Sounds like you want user to enter a date as search criteria to find an existing record. The date is unique? The combobox is on the main form? It is an unbound control? The subform Master/Child link is the date fields? Maybe you should provide db for analysis.

[cooper]

thank you for taking out time to reply! appreciate it:

yes, the user should select a date from a combo box to: 1) find an existing record 2) enter new records which are editable!

combo box is on the subform - and the master/child link is the employee field.

what i want to do is let each user select a date from a combo box - This would then populate the text boxes if records already exist. If no records exist, then new records can be added by that user on that date! A user may come back, select a date and edit any previous records:

Table should show something like this:

for example:

Name Date Activities Available Activities Actioned
John Lemmings 08/08/2011 23 42
Rick Burton 08/08/2011 19 48


so I guess Name and date are primary key, right? I'm uploading the database here - You might have to enter your name in tblEmployees form to activite the "click here to continue" button!

subform is frmtable2:


thank you!!!

[June7]

Had a little trouble with the login because code assumes user ID will be composed of a first and last name, which is not the case on my home PC. But I got past that.

I would never use dates and names as keys. These are criteria to search for and filter records.

The main and subform are linking on employee ID so the display of the subform is already filtered by this linking. You want to do additional search/filter on a date value.

Why is frmTable2 bound to Tableappend? Only the CA and CAC fields are from this table. All of the other fields are in Table2. This is why the date combobox AfterUpdate event does not work. I bound to Table2 and the event code works but CA and CAC textboxes have invalid ControlSource. How does Tableappend relate to this setup?

You also need code to requery the combobox RowSource when moving to another record. Rename the subform container control that holds the frmTable2 to ctrActivities. In the main form's OnCurrent event place this: Me.ctrActivity.Form.Combo107.Requery.

[cooper]

Yes, I want to do additional search/filter on a date value...
something like: the subform recognizes what user is logged in (which is already happening) - then the user selects a date and the records associated with that name and date show. If no records exist, the user can add/edit the records as well.


and I was just playing with the database, and maybe thats why I bounded frmTable 2 to Table append. The main table is tblEmployees and Table2 - rest I was just playing with it. Tableappend is just something I thought might work and thats why I only included a couple of fields.

hope im making sense here. Again, thanks for your insight! appreciate it alot.

[June7]

Did you attempt my suggestions?

If you want the subform to show values from two tables, RecordSource must be a join query. I don't recall the relationship of these tables so not sure if they can be joined.

[cooper]

"Rename the subform container control that holds the frmTable2 to ctrActivities"

what does that mean. I am sorry for my lack of knowledge :s

I added the code to the oncurrent event but it comes with error on ctrActivities :S

[June7]

A subform is created by installing a subform container control on another form. The container control has a SourceObject property. The SourceObject can be a form or a table/query. I always give containers a name different from the object they hold. The error is because the code can't find the name. Click on the edge of what appears to be the subform, this will select the container, click inside the edges and you will select the form within the container.

[cooper]

got it working! but how can I work in such a way that select a date should show the records corresponding to it? what code should i put in the date combo box!!?

[cooper]

oh wait - it works! wow thanks a bunch!

one question:

how should i allow the user to enter a new date and add data according to that date. should i make one text box for date and add records??

[June7]

Try:

Private Sub Combo107_NotInList(NewData As String, Response As Integer)
    Me.Combo107 = Null
    DoCmd.GoToRecord , , acNewRec
    Me![Date] = NewData
    Me.Combo107.Requery
    Response = acDataErrContinue
End Sub
 
Private Sub Combo107_AfterUpdate()
    Dim rs As Object
    Dim dteDate As Date
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ActivitiesID] = " & Nz(Me.Combo107.Column(1), 0)
 
    If rs.NoMatch Then
        dteDate = Me.Combo107
        DoCmd.GoToRecord , , acNewRec
        Me.Combo107 = dteDate
    Else
        Me.Bookmark = rs.Bookmark
    End If
End Sub

I changed the date combobox RowSource to put the date in first column.

[cooper]

works like a charm! thank you sir!

last 2 things:

when I open the form again - the text boxes and date the first record that was entered - how can I make the text boxes and combo box blank when the user opens the form again?


Also, as the user will keep on entering data, the combo box will become huge.. I don't think it would be user friendly after a while - Is there anyway to tackle that problem?

thanks again!

[June7]

1. Bound controls will not be blank unless form opens to a new record. I don't have project with me now but isn't the combobox unbound?

2. Not if you want the other fields available as columns in combobox.

[cooper]

Yes the combo box is unbound - so how do I open the form to a new record everytime? but can opening a form to a new record be a problem?? because if the user logs in another time on the same day, the form save it as a new record?

I just want the date in the combo box - and after a while it will become big.. i guess the user will just have to start typing the date in the combo box as it fills up, right?

[June7]

Can have form always open to new record by setting the DataEntry property to Yes. This means existing records will not be viewable. Eliminates search capability. The code I provided you would have no purpose.

If search capability not required, no need for unbound combobox. Use a bound textbox with input masking for date or date picker.