Retaining drop down value for next record

[bob fitz]

Are you using your form in datasheet view or single form view

 

[dcavaiani]

I totally agree with you Bob! Still would like to understand the 'before/after add/update/timing methods options of this as understanding that interests me, and not having to redesign it.

 

[dcavaiani]

Are you using your form in datasheet view or single form view

It was in a simple datasheet view but I needed to get that out of the hands of my new business partner so he could not mess with the Invoiced date and to maintain the table integrity. Originally, I just had him entering data straight into the table, but took that away from him with the form and made it a bit quicker to enter data. Now, I'm trying to speed the entry up further w/o a complete re-design.

 

[bob fitz]

So it is being used in datasheet view?

 

[dcavaiani]

No the form is hidden, runs from the macro.

 

[dcavaiani]

I did say that I had a checkbox that I selected when I wished to save the data for another record.

Just remove the reference to Me.chkCopy, you do not really need it unless you wish to use it as I do.?

Is the chkbox actually called chkCopy?

so just use

Me![Date_ID].DefaultValue = """" & Me![Date_ID].value & """"

and the same for any other field on the form.

Bob's solution is neater though. I got mine from googling a good few months back.

FWIW my form is a simple Access form. As I tab through the combo boxes and the rest of the fields, Access automatically adds a new blank record, which I then populate.

I had over 13000 records to enter, so was looking for the quickest way to do this, as previously I was using Ctrl & '

-----------------------------
Removing the copy seems to work,except that all the fields I MAY want to duplicate do highlite fine - but WHAT IF I don't want them to repeat ???

 

[Gasman]

Now I see the process by which you are adding records, I went back and tried it again.

Selecting Copy saves the Customer entry. That appears to be on the only one at present that has the required code?

So I see that as working?.

I added the code to VendorName (changing the control name) and that worked as well.??

 

[dcavaiani]

Thank you to everyone who contributed - I need to spend a few more days checking/testing.

 

[dcavaiani]

Gasman, using your code, I can get Customer, Vendorname, and VendorInvoice to "copy" but NOT the PurchaseDate (which is a combo box rather than a list box or text field). Any thoughts?

 

[Gasman]

Gasman, using your code, I can get Customer, Vendorname, and VendorInvoice to "copy" but NOT the PurchaseDate (which is a combo box rather than a list box or text field). Any thoughts?

Both my fields are combobox and it works fine.

I tried it on the PartDesc and that works fine.

I think it is something to do with the calendar control as the code never gets to the afterupdate event.

However if you put it in Private Sub Calendar5_Click() it works.

I do not have enough event handling knowledge to suggest a better solution

Also I had to comment out some of this code. I couldn't find a Description control and had error 'cannot disable control when it has the focus'.?

Why it worked previously I have no idea.?

Edit: Now I realise it will only happen when an error occurs. You need to correct that.

Private Sub Command27_Click()
On Error GoTo Err1
  DoCmd.GoToRecord , , acNewRec
  Exit Sub
Err1:
  'Description.SetFocus
'  Me.Command27.Enabled = False
  MsgBox (Err.Description)
End Sub

 

[dcavaiani]

Gasman, perfect - I will be trying that SOON! Thanks!

 

[dcavaiani]

Gasman - YES that does work for the Purchase date now! No worries with the partdesc field as that would not ever be duplicated. You think there is a way to have a COPY? checkbox work so that the fields will ONLY duplicate after the clicking on the COPY? checkbox when a new record screen comes up AFTER you have just added a record?

 

[Gasman]

Gasman - YES that does work for the Purchase date now! No worries with the partdesc field as that would not ever be duplicated. You think there is a way to have a COPY? checkbox work so that the fields will ONLY duplicate after the clicking on the COPY? checkbox when a new record screen comes up AFTER you have just added a record?

Your Add Record button adds a new record which starts off with empty fields.
To do what you want you would need to find the last record in the table and use that to populate the controls on the form. This would be ideal for the very first record you add.

However the way you are working at the moment, you could save the controls to public variables each time, and if the Copy checkbox is selected copy them back to the controls.?

 

[dcavaiani]

Gasman - Thanks again - I think I like that and that I can handle that by simply putting that code inside either an on-click or on-enter event of the chkCopy.

 

[Gasman]

The drawback with that is you would need to click it to copy each time. :banghead:

The 'professionals' in my previous workplace supplied us with such a checkbox when we requested some way to keep the data, as generally only one field was ever changing. Previously we had to re enter all the data, each time we added a record. Obviously no UAT testing involved.

However it was almost as annoying having to click that checkbox each time for every record.

I thought the checkbox was a good idea, but to leave it set, until the user clears it and that is what I implemented for my database. Previously I was using the Ctrl + ' shortcut.

Put yourself in the shoes of those who have to use it and consider whether you'd be happy to do whatever all day.?

 

[dcavaiani]

OK - so how would the code look for BLANKING out all the fields when Clicked?

 

[Gasman]

I personally would leave it as you have now.

However in the click event of the checkbox, clear the fields if the checkbox is not set?

If the checkbox is not set, the fields are not kept anyway, so no code required really, unless you have other ideas?

 

[dcavaiani]

So, the default of the chkcopy must be ON and there must be 'blank out' code for the fields within its event - As the fields copy now by using each fields AFTER UPDATE event, the chkcopy has no function? The chkcopy actually does nothing now.

Private Sub Customer_AfterUpdate()
Me![Customer].DefaultValue = """" & Me![Customer].Value & """"
End Sub

Private Sub VendorName_AfterUpdate()
Me![VendorName].DefaultValue = """" & Me![VendorName].Value & """"
End Sub

Private Sub VendorInvoice_AfterUpdate()
Me![VendorInvoice].DefaultValue = """" & Me![VendorInvoice].Value & """"
End Sub

 

[Gasman]

Yes, as per my original code, you test whether chkCopy is set. If so, save the data, else do nothing.

It is up to you what the default for the chkCopy is set to. Think on how often you would need to change it.

It might be a little untidy, but if you think about it, there is no real harm done if the values are saved all the time, as if they are not what the user wants, they would have to select their required entries anyway when the entries are blank, so you are not creating any more work for them, it is just not that intuitive to use.

 

[dcavaiani]

Gasman, got that working perfectly. Why would the opposite not work with just the code below?

Private Sub chkCopy_AfterUpdate()
If Not Me.chkcopy Then
Me![Customer].DefaultValue = ""
Me![VendorName].DefaultValue = ""
Me![VendorInvoice].DefaultValue = ""
Me![PurchaseDate].DefaultValue = ""
End If
If Me.chkcopy Then
Me![Customer].DefaultValue = """" & Me![Customer].Value & """"
Me![VendorName].DefaultValue = """" & Me![VendorName].Value & """"
Me![VendorInvoice].DefaultValue = """" & Me![VendorInvoice].Value & """"
Me![PurchaseDate].DefaultValue = """" & Me![PurchaseDate].Value & """"
End If
End Sub