Recordset question

[Zaeed]

Is it possible to fill out multiple forms with a recordset?

What im trying to do is fill a recordset with multiple records, and then dump that onto a form which is set to view Continuous Forms or Datasheet.

I've tried... and failed.

can it be done?

 

[ajetrumpet]

What have you tried? What exactly failed?

Remember that forms are just temporary holders of data. Your description is really quite vague. I'm afraid you'll have to provide more information about the process you would like to be using and what the outcome should be (in specific detail).

 

[Zaeed]

Ok, i have a subform which lists tasks. The tasks relate to a change. Multiple (no set number) of tasks for a change.
What i want to do is extract from the tasks table the tasks that relate to the current change, and display them in the subform. I want to do this in an unbound manner, so that tables are only updated when the user clicks the save button.

The tasks then need to be able to be modified or added to.

I currently have the subform setup so that it has three text fields in a row, which are displayed as continuous forms, so a new line of text fields appears below the previous. This is the preferable method, however I cannot seem to fill this form in with the recordset.

I've tried filling a recordset with the required tasks, but was unable to transfer more than one record from the recordset to a continuous form.

I would greatly appreciate help with this as it is driving me mad.

 

[ajetrumpet]

What i want to do is extract from the tasks table the tasks that relate to the current change, and display them in the subform. I want to do this in an unbound manner, so that tables are only updated when the user clicks the save button.

I think I get what you're wanting to do, but I believe there is a better way to do so.

Here is what I envision:

*A horizontal row of text boxes on a coninuous form, each row respresenting a record from the "tasks" table.

If that is the right "picture", then the question still remains "what do you want to do with the data when you get it in this format?". I can see why setting data up this way could be useful, but I think there are more elegant ways to do whatever you need to do. These alternatives would cause you less headaches as well. Can you describe in more detail why you want this setup and what you plan to do with the data? If, from this statement...

What i want to do is extract from the tasks table the tasks that relate to the current change, and display them in the subform. I want to do this in an unbound manner

...you're trying to set up a way in which you can edit data (records) in the subform that pertain(s) to the "change" value, then why not just set up a filter for the subform, and bound the form to the "tasks" table? Does this have to be an unbound process? Why? You should know that, just because a form (or any of its controls) is/are bound to a table, that does not constitute automatic updating. If fact, any updating or changes that occur on bound objects can be cancelled or approved at any time with the use of Visual Basic code.

 

[harleyskater]

I am good at this, if you post a sample DB I can help : )

 

[Zaeed]

Thanks for yor help guys...

Your right in your assesment ajetrumpet. Basically the idea is that when you create a new change, that change has multiple tasks required to complete the change. i.e.
1: open the draw
2: pick up a pen
3: write your name

I want to have my subform list these tasks when viewing a previously submitted change, with the option to modify/add to the list. And when a new change i made have a blank list sitting there.

There are 3 key peices of data that need to be displayed for each task, its number (starting at 1 for each new change), a description, a completion time, and who its assigned to.

The reason I am interested in making it unbound etc is because this is designed for a multi user environment, so I want to avoid the issue of two people editing a table at the same time.

How does one control the updating of a bound form?

And thanks harleyskater, but i'd rather complete this myself, as i'll gain a better understanding of whats going on.

 

[Zaeed]

I'll also be filling in the change form itself from a recordset. And only one change can be displayed at a time. To go to a different change form, the user has to cancel or save the currently open one, then either open a new change or select another one from a list.

just to clarify

 

[ajetrumpet]

Basically the idea is that when you create a new change, that change has multiple tasks required to complete the change. i.e.
1: open the draw
2: pick up a pen
3: write your name

I want to have my subform list these tasks when viewing a previously submitted change, with the option to modify/add to the list. And when a new change i made have a blank list sitting there.

This sounds like an autoform setup that has been created from an existing one-to-many relationship, with the joined field filtering the subform based on the main form's current record (this is a process you can do through the autoform wizard).

The reason I am interested in making it unbound etc is because this is designed for a multi user environment, so I want to avoid the issue of two people editing a table at the same time.

I'm pretty sure you can do that, but I think that's a security issue, not the issue at hand here. That's just like using prevention of editing a file when it is already opened by another user (locking).

How does one control the updating of a bound form?

Through the use of code. Commands and syntax lines that come to mind (and would be helpful) are things like:

Me.Undo, Me.Dirty, MsgBox, and many many different condtional statements!!

I think I said this in another thread recently too, and it is relevant here. "Me.Dirty" is all about forms, and it's all about the initiation of data editing by the user, but for the property to actually be relevant, movement and keypresses have to be performed on a BOUND form or control! I was surprised when the last person I said this to didn't know that! Anyway, that is a good thing for you to know, because it is a conditional argument that I have used a little bit to issue warning messages. But, it may not be relevant to you, so if it's not I apologize.

 

[Zaeed]

Ok, i think I see where your going.

Are there any previous examples i could look at as a guide? Im not sure how to work the Me.Dirty statement to do what i want.

 

[ajetrumpet]

Matt,

I don't know if Me.Dirty is what you want either, but it probably isn't. And if so, it is probably not the most important thing here. But at any rate, the attached sample is in 2000 format, and it is the same file that you will find here. The only difference with this one is that I have added a message box warning to the Me.Dirty property of the "Order Entry" form.

It is just for reference. You'll notice when you have "initiated" an edit (so to speak), because a message box will pop up to warn you. To see the difference between "dirty" actions, and "non-dirty" actions, do this:

1) Open the form, and start clicking around. Even use the combos to drop the menus down, but don't select anything from the lists. Just click everywhere.

2) Now, open the form again, and start typing in the "Order Quantity" text box. See the warning message? It was issued to you because you started editing in a bound object, weather it be a control, or the actual form itself. The quantity text box is bound to one of the source table's fields. Thus, you have edited a data source (dirtied it).

3) Open the form one more time, and start typing in the text box that is right below the "Add Line Item" button. You don't see the message box because it is an unbound object.

Anyway, just a little learning for you. As far as knowing what you want to, maybe if you want a little guidance on it, you can say what you have done, or want to do, one step at a time? I have already said what I think you want, and the way I would do it. What you do from here I think, is up to you.

 

[Zaeed]

cheers adam

 

[Zaeed]

um, on load the form gives me an error.

Can't go to the specified record

The problem is the Order Entry form load where it sets the focus on Me.txtid

 

[ajetrumpet]

There is no .SetFocus code on Me.txtID Matt, so I'm not sure what you're talking about...

Can you navigate the form at all? Can you at least close the error window and go to the form? Is this what you need? And does it help?

I know about "an" error. I haven't opened that file in a while, and when I did tonight I got a strange error message that I had never seen before. But, upon subsequent tests of opening the form and even the DB, I didn't get it again.

 

[Zaeed]

When i open the form 'Order Entry' it presents a Run-time error '2105' You can't go to the specified record.

The debug goes to this portion of code

  With Me.txtid
    .Enabled = True
    .Locked = False
 [COLOR="Red"]   .SetFocus[/COLOR]
  End With

Am i doing something wrong..
Im running access 2003

 

[Zaeed]

hmmm, it seems that i was running the file from within the .zip

my bad..

btw, after some work i have my form working fairly much the same as this example works. My next step is to manage new changes, instead of editing current ones. This example confirms my idea of using temp tables to store the data until update.

Cheers Adam

 

[ajetrumpet]

after some work i have my form working fairly much the same as this example works. My next step is to manage new changes, instead of editing current ones.

So, what are you going to do now? Are you looking for some more help with it? Do you have an example file you can post up? I'd like to see how you're doing so far...

 

[Zaeed]

yeah, i have a few more things to work out first, then i have 2 mamoth tasks to work out. One is a system of determining who gets contacted based on a set criteria, which is more of a logistical issue than a db one; and discovering a good risk management system.

fun times.
sample db to come once I get more functionality running + clean up the coding (its quite embarrasing atm)

 

[Zaeed]

Heres a quick question.

In my subform, I have a date field, which when clicked opens an activex calendar. How do I then set that field (record?) to the date.
My calendar code has a select query which is set on the click command (i.e. form A is reference 1 etc), and this works fine when setting the main forms date field using

[Form_frm_Change]!frm_Change_Date_Start.Caption = UserSelectedDate

However, since this particular date field is in a subform, which is being displayed as a continuous form, it is proving to be difficult.

I have so far tried the above approach like this,

[Form_frm_Change]![Form_tbl_Tasks_subform]!frm_Change_Date_Start.Caption = UserSelectedDate

with no success.

My current attempt uses SQL insert commands to do the job, but this gives me a parameter error, which i can't figure out.

If Not newChange Then
strSQL = "UPDATE tbl_Tasks SET tbl_Tasks.DateRequired = UserSelectedDate WHERE TaskNumber = " & GetTaskNumber() & " AND ChangeNumber = " & GetChangeNumber()
Else
strSQL = "UPDATE tbl_Temp SET tbl_Tasks.DateRequired = UserSelectedDate WHERE TaskNumber = " & GetTaskNumber() & " AND ChangeNumber = " & GetChangeNumber()
End If
db.Execute strSQL

Anyone know how to make this work?

 

[ajetrumpet]

In my subform, I have a date field, which when clicked opens an activex calendar. How do I then set that field (record?) to the date.

Trying to set the date with code? I don't understand this. If you want to pick a date for a field, why can't you use the date picker for form controls?

 

[Zaeed]

Trying to set the date with code? I don't understand this. If you want to pick a date for a field, why can't you use the date picker for form controls?

What do you mean by 'date picker for form controls?

I have a calendar form which is used for a few different things, so when i click on the date text box it opens the calendar in a dialog box, you select the date and hit ok, which then inserts that date into the text field that you clicked.

It works fine for the main form, but I cant make it work for the subform