Best way to store info on a form

[Design by Sue]

I have a form that has the following controls:

SOPNumber
EmployeeName
DateCompleted
TimeCompleted

The form is bound to a table. When a record is saved to a table normally the form is cleared. If the training session was completed by more than one employee it would be best to return to the form with the EmployeeName blank but the other 3 controls still holding the information from the record that was saved. I hope to put the code on a save button. Any suggestion of what method would work for this?

Once again I hope that makes sense.

 

[theDBguy]

I think you're asking how to set the default value property of those other controls. Right?

 

[GPGeorge]

There are two possible interpretations, I think. One is the Default Value Property that would apply to ALL new records, but the other might be variable values, which are part of a group of records having values in common and needing to be repeated for just that group.

Which is the case for your application?

 

[LarryE]

but the other 3 controls still holding the information from the record that was saved. I hope to put the code on a save button.

Let's say you have Control1, Control2 and Control3. You need to capture the OldValue of each control to use when you add a new record. Then when you add a new record:

Me.Control1 = Control1.OldValue
Me.Control2 = Control2.OldValue
Me.Control3 = Control3.OldValue

TextBox.OldValue property (Access)

Office VBA reference topic

learn.microsoft.com

 

[Design by Sue]

Let's say you have Control1, Control2 and Control3. You need to capture the OldValue of each control to use when you add a new record. Then when you add a new record:

Me.Control1 = Control1.OldValue
Me.Control2 = Control2.OldValue
Me.Control3 = Control3.OldValue

TextBox.OldValue property (Access)

Office VBA reference topic

learn.microsoft.com

This sounds like what I need to do - I will look over the link this evening and check it out. Thanks

 

[Design by Sue]

There are two possible interpretations, I think. One is the Default Value Property that would apply to ALL new records, but the other might be variable values, which are part of a group of records having values in common and needing to be repeated for just that group.

Which is the case for your application?

I don't think default value sounds correct unless it can be set in code and then cleared when the user clicks another button that will move to a new cleared record. I am not clear about the 2nd description - I am thinking the OldValue from above might be the solution but will try to figure it out in a bit.

 

[Mike Krailo]

I would use a list box of EmployeeName values. Then you just enter the other three values on the form once and click "Add New Training" button. The code would then append all of the training records in one go. I do this all the time on a scheduling form that has many sessions of scheduled time blocks on different dates. I just select the dates from a multi select list box and press the button to append the scheduled training.

You would need to validate the values inserted in the form to avoid errors. I made up a sample of both a normal entry form and one that is unbound and allows you to do bulk entry.

 

[The_Doc_Man]

I am thinking the OldValue from above might be the solution but will try to figure it out in a bit.

OldValue is updated to match Value after a record is saved and the form undergoes the "Current Event" so I doubt it will be useful to you.

 

[Gasman]

Here is how I did it.
You can see how many records I manually entered using this method.
Repeats were for Date, Ship and Rank

Private Sub Date_ID_AfterUpdate()
    If Me.chkCopy Then
        Me![Date_ID].DefaultValue = """" & Me![Date_ID].Value & """"
    Else
        Me![Date_ID].DefaultValue = 0
    End If
End Sub

Taken from Allen Browne's site http://allenbrowne.com/tips.html
You would do well to browse that site.

 

[Gasman]

I don't think default value sounds correct unless it can be set in code and then cleared when the user clicks another button that will move to a new cleared record. I am not clear about the 2nd description - I am thinking the OldValue from above might be the solution but will try to figure it out in a bit.

DefaultValue is precisely for NEW records?

 

[Design by Sue]

Here is how I did it.
You can see how many records I manually entered using this method.
Repeats were for Date, Ship and Rank

View attachment 115306

Private Sub Date_ID_AfterUpdate()
    If Me.chkCopy Then
        Me![Date_ID].DefaultValue = """" & Me![Date_ID].Value & """"
    Else
        Me![Date_ID].DefaultValue = 0
    End If
End Sub

Taken from Allen Browne's site http://allenbrowne.com/tips.html
You would do well to browse that site.

Trying to understand this as it does look like the easiest way to go. I looked at the allenbrowne site but can't see this. (not sure what to lookfor there is a lot there!) Do I understand it correctly that the line
Me![Date_ID].DefaultValue = """" & Me![Date_ID].Value & """"
maintains the value that was entered into the form?

So if I set code like this for all of the fields I want to maintain the previous information it will do what I want?

Thanks for this suggestion - I hope I can make it work.

 

[Design by Sue]

I would use a list box of EmployeeName values. Then you just enter the other three values on the form once and click "Add New Training" button. The code would then append all of the training records in one go. I do this all the time on a scheduling form that has many sessions of scheduled time blocks on different dates. I just select the dates from a multi select list box and press the button to append the scheduled training.

You would need to validate the values inserted in the form to avoid errors. I made up a sample of both a normal entry form and one that is unbound and allows you to do bulk entry.

I looked at your database - thank you. Not sure I understand. But using your comments I was trying to set up an append query to append the record to the table instead of adding a new record using the Do.Cmd control (which clears the form) Instead of opening a separate form, I was trying to use my current form and add a button to run this append query. But as I have had this issue in the past, I can't get an append query to see the fields for the DateCompleted and TimeCompleted - even though I have double checked the names of the fields. I ran into this problem on this before and was not able to solve it back then so had to go anther way. I wish I could figure out why these 2 fields will not be picked up in the query.

 

[Design by Sue]

I looked at your database - thank you. Not sure I understand. But using your comments I was trying to set up an append query to append the record to the table instead of adding a new record using the Do.Cmd control (which clears the form) Instead of opening a separate form, I was trying to use my current form and add a button to run this append query. But as I have had this issue in the past, I can't get an append query to see the fields for the DateCompleted and TimeCompleted - even though I have double checked the names of the fields. I ran into this problem on this before and was not able to solve it back then so had to go anther way. I wish I could figure out why these 2 fields will not be picked up in the query.

Again thank you - I think I am able to make Gasman's suggestion work so don't put any extra time in on this at this point

 

[Design by Sue]

Trying to understand this as it does look like the easiest way to go. I looked at the allenbrowne site but can't see this. (not sure what to lookfor there is a lot there!) Do I understand it correctly that the line
Me![Date_ID].DefaultValue = """" & Me![Date_ID].Value & """"
maintains the value that was entered into the form?

So if I set code like this for all of the fields I want to maintain the previous information it will do what I want?

Thanks for this suggestion - I hope I can make it work.

It appears that I did understand and have been able to make this work!!!! YAY but now I need to clear the Employee Name (a combo box) but when I tried DefaultValue = 0 for that it did not clear the field. Any thoughts on how to clear this one field?

 

[LarryE]

OldValue is updated to match Value after a record is saved and the form undergoes the "Current Event" so I doubt it will be useful to you.

Yeah that is right. The OP is going to have to set the default values before creating a new record.

 

[The_Doc_Man]

Yeah that is right. The OP is going to have to set the default values before creating a new record.

Or store the information in the form's class module declaration area to check whether anything was stored in that area. And if you are going that route, the Copy Previous Record checkbox has to be unbound or it will be "tapped" by the Current event.

 

[Design by Sue]

Yes daefault values is working per Gasman's instructions (thank you - I am poking at it and am getting close to what I need.)

But as I said I can't get the Employee Name combo box to clear to show no name. Can some one tell me what I would use to do this? DefaultValue = "" is not working nor is DefaultValue = 0. I am sure now that default value is not the way to clear this field but I am not sure what is.

 

[The_Doc_Man]

Clearing a bound field implies changes to be made to the underlying record. However, if you are creating a new record bound to a form, Access should clear the record and then the Access part of the Current event will load the controls with an empty record.

Clearing an unbound control isn't hard if at least some of the controls ARE bound so that you can have a Current event. However, if the entire form is unbound, there will be no Current event because there is no recordset with which to synchronize. In that case, your data flow gets considerably more complex.

Specifically to clear a combo box that doesn't have a default value, you could try a comboboxname.Undo - which should reset it to "no selection."

 

[LarryE]

Yes daefault values is working per Gasman's instructions (thank you - I am poking at it and am getting close to what I need.)

But as I said I can't get the Employee Name combo box to clear to show no name. Can some one tell me what I would use to do this? DefaultValue = "" is not working nor is DefaultValue = 0. I am sure now that default value is not the way to clear this field but I am not sure what is.

EmployeeName = Null
should do it.

 

[Design by Sue]

EmployeeName = Null
should do it.

I get an error message stating I can't assign a value to this object.