Populating a multiple textboxes on a form

[Richhol65]

Hi

Hopefully someone can help me.

I have a form and I am not sure of the best way to populate the multiple fields on it.

The form consists of a textbox to select a date (txtDate) and then once this date is selected I want the multiple textboxes on the form to be populated from data I have in a table.

The name of each textbox is a concatenation of "txt" the number of the person which is a number from 1 to 4 and then a time eg for 12 Noon it would be 1200 so the textboxes are from txt10800 to txt42000 i.e. each person has textboxes from 8.00am to 8.00pm in half hour intervals.

I need to use two tables to populate these textboxes as in the main table there are codes that have a relationship to fields within another table that holds attributes for that code. (eg code 123 could equal Service A in the second Table but 123 is what is held on the main table) Also in the main table I have a calculated field that creates the name of the relevant textboxes on the form.

If there is a textbox that will not be populated as there is nothing in that time period then I would like this to be left blank or Null.

Am I best creating a query and binding the form to this query or to leave it unbounded and use a recordset within VBA

Also what is the best way of using either

I am using Access 2013

Thanks

Rich

 

[highandwild]

I think that your database needs to be restructured. Each time slot for each person for each date needs to be a new record in a table. This different aproach may mean additional work up front but it will pay dividends later on. Have you heard of normalisation? Try reading this http://support.microsoft.com/kb/283878

Getting the structure right will mean that all of the work subsequent to that will be easier.

You do not give a lot of information about what you are trying to do with this project. More information may elicit more help.

 

[Alansidman]

Look at this link on database structure.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

 

[Richhol65]

Hi Both

Each record in my main appointment table is unique in the way you say. I probably didnt express myself well enough.

The main table structure is as follows:

ID
Customer Name
Stylist/Employee
Appointment Date
Appointment Day
Appointment Time
Service Required Code
Appointment duration
Price
Calculated field for name of textbox on main form

So surely that is similar to the structure you suggest ?

Thanks

Rich

 

[Richhol65]

I think I am getting a bit nearer to populating my form but I am struggling to quite get the code correct.

My form is called DailyDiary and I am trying to populate it with records from a query called PopulateDailyDiary.

The query has three fields, they are:

Textbox - This is the name of the textbox on the DailyDiary From
AppDate - This is the date of the Appointment
Textfill - This is the information that I want to appear in the textbox on the Daily Diary Form

I am using the following Code:

Private Sub txtDate_AfterUpdate()
Dim i As Integer
Dim arrTXT As Variant
Dim rs As Recordset
Dim db As Database

i = 0
arrTXT = Array(Me.txt10800, Me.txt10830, Me.txt10900, Me.txt10930, Me.txt11000, Me.txt11030, Me.txt11100, Me.txt11130)


Set db = CurrentDb()
Set rs = db.OpenRecordset("PopulateDailyDiary", dbOpenDynaset)
strSQL = "SELECT textFill FROM PopulateDailyDiary WHERE [Queries]![PopulateDailyDiary]![AppDate] = me.txtDate;"

With rs
    If .BOF Or .EOF Then
        .Close
        
    Else
    
        Do Until .EOF
           arrTXT(i).SetFocus
           Me.ActiveControl = .Fields("DailyDiary")
           i = i + 1
           .MoveNext
        Loop
    End If
    .Close
    
    End With
    Set rs = Nothing
    Set db = Nothing
    

Me.txtDay = Format(txtDate, "dddd")


End Sub

I am getting an error:

Runtime Error '3265' Item not found in this collection

The code highlighted in this error is :

Me.ActiveControl = .Fields("DailyDiary")

What am I doing wrong

Thanks

Rich

 

[Richhol65]

Ok

I have now got the code almost to work and it now fills the textboxes on the form but just selects them in order they are in the table even though I thought my SQL was filtering by date. Also it doesnt seem to be putting the correct line in the correct textbox.

The code is attached to the After Update event of the form

I have multiple textboxes on the form e.g. txt10800 is the appointment box for stylist 1 on that day at 08:00.

I also have this in a field within the query I am using "PopulateDailyDiary"

The three fields in this are
Textbox - (txt10800 etc)
AppDate - (Appointment Date)
Text Fill - (The text I want in the textbox on the form)

The code I am using is looping through the query and just putting the first record in the first box whether that relates to the date specified or not.

The code I am using is:

Dim i As Integer
Dim arrTXT As Variant
Dim rs As Recordset
Dim db As Database

i = 0
arrTXT = Array(Me.txt10800, Me.txt10830, Me.txt10900, Me.txt10930, Me.txt11000, Me.txt11030, Me.txt11100, Me.txt11130)


Set db = CurrentDb()
Set rs = db.OpenRecordset("PopulateDailyDiary", dbOpenDynaset)
strSQL = "SELECT textFill FROM PopulateDailyDiary WHERE [Queries]![PopulateDailyDiary]![AppDate] = me.txtDate;"

With rs
    If .BOF Or .EOF Then
        .Close
        
    Else
    
        Do Until .EOF
           arrTXT(i).SetFocus
           Me.ActiveControl = .Fields("textfill")
           i = i + 1
           .MoveNext
        Loop
    End If
    .Close
    
    End With
    Set rs = Nothing
    Set db = Nothing
    

Me.txtDay = Format(txtDate, "dddd")


End Sub

What do I need to do to this code so that only the lines that relate to that textbox on that day populate the textboxes

Thanks

Rich