Populating multiple fields on a form using a recordset

[richhol65]

Hi



Hopefully someone can help me.

I am working on a database that is going to be used to store and show appointments at a friends hairdressing salon.

I have an appointments table that holds the following details:

ID this is a concatenation of Date, time, stylist number and customer name
CustName Customer Name
StylistNo Stylists name
AppDate Appointment Date
AppDay Appointment Day
AppStartTime Time of Appointment
ServiceCode Code number of Service required
AppDuration Appointment Duration in Minutes
Price Cost of Service
Textbox Name of the corresponding textbox on the DailyDiary form

I have a form called DailyDiary and on this there are a textbox call txtDate that is a date selection then I have textboxes that relate to half hourly appointments for each stylist. these are named as follows:

For stylist 1 an appointment at 08.00 would be called txt10800 where 10800 is stylist 1 at 08.00
For stylist 3 an appointment at 11.00 would be called txt31100 where 31100 is stylist 3 at 11.00
etc

When a date is entered into txtDate I have an afterupdate event that should fill all the textboxes from a query called PopulateDailyDiary.

PopulateDailyDiary has the following fields:

TextBox the name of the corresponding textbox on the DailyDiary form
AppDate the date of the appointment - filtered to agree to the date selected in txtDate on the DailyDiary form
TextFill the data that I want to fill the corresponding textbox on the form.

The code I am using is as follows but I keep getting a 3061 error and then an out of range error

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] = [forms]![DailyDiary]![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

Can someone help me with the code to see what I have done wrong

Thanks

Rich

[June7]

Why do you need code to populate a form? The form is not bound? Why not?

Which line causes the error? Have you step debugged? Refer to link at bottom of my post for guidelines.

[richhol65]

Hi June7

They want the form to be interactive so when they change the date they want the data to change, what I have also done is when you click on a blank textbox it takes you to a form to create an appointment.

i did debug but I will do it again thank you, Once I have debugged and corrected the code tI take it that will get rid of the 3061 error but once that is sorted what about the out of range error ?

[June7]

Bound forms are interactive.



What is the 3061 error message? What line causes it?


Don't include variables within quotes. That results in the variable as literal text, not the content of variable. Reference to control on form is a variable. The construct for strSQL references a control. However, the strSQL is not used anywhere. Why is it there?


If you step through the loop, will probably discover why exceeding array bounds.

[Dal Jeanis]

This line

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

loads an array with whatever is currently in the form. The recordset loop then changes that array. Nothing sets the text boxes on the form to the new values of the array.

The code seems to assume that there will be exactly 8 text fields returned by your query.

It also seems like your design is depending on the sequence of the records in table PopulateDailyDiary staying the same. Even if you got this code working as is, I don't see anything in your code that will keep appointment text from randomly moving around - nothing in the storage method attaches an appointment time to the notes that are in txt10930, and keep that text before txt11000 and after tst10900.

Stepping back from those issues, it looks like your underlying problem is that you decided to build it all from scratch rather than let Access do what it does naturally and well.

What is the structure of PopulateDailyDiary? What is the structure of the form? Will the form only show a single day?

[richhol65]

This line

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

loads an array with whatever is currently in the form. The recordset loop then changes that array. Nothing sets the text boxes on the form to the new values of the array.

The code seems to assume that there will be exactly 8 text fields returned by your query.

It also seems like your design is depending on the sequence of the records in table PopulateDailyDiary staying the same. Even if you got this code working as is, I don't see anything in your code that will keep appointment text from randomly moving around - nothing in the storage method attaches an appointment time to the notes that are in txt10930, and keep that text before txt11000 and after tst10900.

Stepping back from those issues, it looks like your underlying problem is that you decided to build it all from scratch rather than let Access do what it does naturally and well.

What is the structure of PopulateDailyDiary? What is the structure of the form? Will the form only show a single day?

Hi Dal Jeanis

I have composed the code from snippets here and there and have obviously got it totally wrong from the sounds of it

I would like the form populated when a date is selected in the txtDate field on the DailyDiary form from a query called PopulateDailyDiary

PopulateDailyDiary consists of 3 fields:

Textbox - which is the name of the corresponding textbox on the form
AppDate - which is the Appointment date that is filtered when a date is selected on the form
TextFill - which is the text that I want to appear in the corresponding textboxes on the form

And yes it is just to show one days appointments

Does that help at all

Rich

[Dal Jeanis]

Instead of the name of the textbox to load, why not use the appointment time? Then you can use a continuous form to display the appointments, use a filter on date to limit to a particular day, and you won't have to code much at all.

[richhol65]

Instead of the name of the textbox to load, why not use the appointment time? Then you can use a continuous form to display the appointments, use a filter on date to limit to a particular day, and you won't have to code much at all.

Dal Jeanis

It isn't just appointment time it is also stylist number as well as there are 4 stylists so each textbox currently relates to a stylist and a time.

I also have functionality that if an empty textbox is clicked it opens a form to add data to the main appointment table but populates this with partial details i.e. date, day, time and stylist name which is related to the textbox and if the textbox is populated it would populate a form fully but would be an edit rather than add.

So they are using this form as a sort of switchboard form to go to different forms

That is why I have gone the route I thought.

Could i bind the form to either the query or the table and get the textboxes populated when a date is selected and if so what would be the best way ?

Thanks

Rich

[Dal Jeanis]

Okay, you're doing a lot of work manually - You have built data structure limitations into your forms and vice versa. I can see how to do the screen such that it wouldn't have those complexities, but that would be a lot of rework.

I have an intermediate idea that maybe would help.

Okay, so look at it this way: Each textbox can be bound to a unique individual query.

Let's suppose the entire form is for Date X
Let's suppose the top row is for Time Y
Let's suppose the left column is for Stylist Z
Then the top left textbox can be bound to a query like:

SELECT MyText FROM MyTable Where MyApptDate = X AND MyApptTime = Y AND MyStylist = Z;

And any time the date is changed (or the Stylist for that column is changed) then you can use VBA to requery the appropriate textboxes (or all of them).

textXYZ.requery

Changes to the data in a textbox would automatically change the underlying database field. If you didn't want that, you could set the textbox.locked property, and handle an attempted change by opening another form.