Macro to copy and paste field name using button

[smile]

Hi, please see my attached database. rename to rar

I have table tbl_items where I enter items and another table tbl_poppular for popular entries I would like to be able to click a button see tbl_items form and copy the selected value from list box to text field.

Also I would like to make memory buttons "add to memory" +M and "paste from memory" -M like on a calculator to store some fields in memory and be able to reuse on next record. I have no idea how to do this.

 

[CyberLynx]

Hi Smile,

To do what you like with regards to the likeness of memory buttons, you will need to create two Form Wide Variables in the Declarations section of the Forms' code module, like this

Option Compare Database
Option Explicit

Dim MemItemName As Variant
Dim MemPurchaseDate As Variant

Name the M+ button for the Item name: ItemMemPlus
Name the M- button for the Item Name: ItemMemMinus

In the OnClick event For the M+ button:

MemItemName = Me.Item_name

and in the OnClick event For the M- button:

If Len(Nz(MemItemName, "")) > 0 Then Me.Item_name = MemItemName

Now..Name the M+ button for the Item name: DateMemPlus
Name the M- button for the Item Name: DateMemMinus

In the OnClick event For the M+ button:

MemPurchaseDate = Me.purchase_date

and in the OnClick event For the M- button:

If Len(Nz(MemPurchaseDate, "")) > 0 Then Me.purchase_date = MemPurchaseDate

That should take care of that feature. Now for the applying the item name in List to the Item Name Text Box, place this line of code into the DblClick event for the List Box:

   If IsNull(Me.Item_name) = False Then
      If MsgBox("Are you sure you want to change the Item Name" & vbCr & _
                "data from:" & vbCr & vbCr & _
                Me.Item_name & vbCr & vbCr & _
                "TO:" & vbCr & vbCr & _
                Me.ItemsList.Column(0), vbQuestion + vbYesNo, _
                "Are You Sure?") = vbNo Then
         Exit Sub
      End If
   End If

   Me.Item_name = Me.ItemsList.Column(0)

Keep in mind...you need to name the List Box Control to ItemsList.

There you have it. When you double click on a item within the List Box it will be placed into the Item Text Box. If there is already an Item in the Text Box then the User is prompted with a "Are You Sure" message box.

Attached to this post is your Sample DB demonstrating the above.

On another note: WinRAR will create ZIP archives.

.

 

[smile]

Big thanks, just checked my post and I'm very pleased with the solution.
Very detailed explanation.

Would it be possible to change the "plus" button color to green to show that it has information stored in memory?

Also I have to add multiple same records to the table, I think that a user should click standard "save record" button but it needs to check value in unbound textbox "record_value" or something and add amount entered there.

The textbox "record_value" should have 1 by default so user can add single records normally and if number is entered more than 1 there should be warning pop-up box asking are you sure, multiple records will be added. This is not possible to undo. etc.

Thanks for your time.

 

[CyberLynx]

Here is your Sample DB with the required options you seek.

Place the mouse pointer over the Mem buttons and read the Control Tip Text that is displayed on all buttons....before storing to memory (so to speak) and after storying to memory.

.

 

[smile]

The memory buttons work fine, however the multiple record insert button is not.

If I type in number 5 to insert the record 5 times, I get 7 instead. If I enter 2 I get 3 etc.

The concept is that by default there is 1 entered in "number record to copy"
user uses the copy record button as a normal add new record button.

However if user enters larger number than 1, the record gets entered into the table exact number of time user enters in "number record to copy" box.

I see that in comments you check if there are similar records added before why is that? My table will have repeating entries because I will enter same record multiple times?

--------

I'm just constructing a database purchased vs. sold items and in my db there is no quantity field. Simply user needs to enter item records. IF they purchase a box of paper and it holds 100 sheets of paper and the user decides to sell the paper by sheet. Then he needs to create 100 records (one for each sheet). When item is sold it is counted and by lookup table connection I can get remaining items in the database (unsold). I made filter to sell only remaining items to avoid selling the same twice.

The paper is just an example. My db tracks each items unique number field that's why I store each of them as a new record.

Hope it makes sense.

 

[CyberLynx]

If I type in number 5 to insert the record 5 times, I get 7 instead. If I enter 2 I get 3 etc.

This is an odd one. I can not duplicate that problem with the Sample I posted, it works just fine. If you look at the For/Next loop which generates the copied Records you can clearly see that is is based off of the number contained within the Text Box beside the Copy Record button. If there is the number 5 within that Text Box then the Code can only make 5 copies. The only way it can make more is if the number in that box changes for some reason.

Did you experience this within the modified sample I posted?
Or did you experience this problem after you had implemented the code into your original Database?

As I have said, I can not duplicate the problem you are experiencing. I would have to see the DB you are currently running.

However if user enters larger number than 1, the record gets entered into the table exact number of time user enters in "number record to copy" box.

This is yet even a stranger one to me since it appears to be in complete contradiction from the comment you made earlier. I'll just leave it at that.

I see that in comments you check if there are similar records added before why is that? My table will have repeating entries because I will enter same record multiple times?

Well...the simple answer to that is the fact that I was not there with you during the concept and modeling of your Database. Understandably, as mentioned in a previous post, you want to make multiple copies of a record. To what extent that may be I don't know.

The feature is an option in code which can be easily removed. Please don't hesitate to do so on my account. Make the code function exactly as you like but please keep in mind that what may not be required for you may be exactly what someone else needs. When your Sample DB is posted many people will download it and chances are, will use some of your ideas. Each person that decides to utilize the concepts or code mechanisms within the sample will do so and make necessary modifications to suit their specific needs.

.

 

[smile]

Well, I tried your database today and it works fine

Then I tried it on another PC that I will try to implement it on, it gave this error:

Runtime error '3075'

Syntax error in date in query expression '[Item Name]='testentry 123'
AND quantity=NULL AND price=NULL AND purchase_date=
#2008.10.28# AND Sold='.

Debug stops here :
Res = DCount("[ID]", "[tbl_Items]", StrgSQL)

My regional settings on this computer use "," instead of "." as decimal symbol.

 

[smile]

BTW did you do those SQL statements in notepad or do you use some sort of editor?

In my real database I will have different fields and I'm unsure I can make it work. Can I send you PM and ask for help to take a look at stripped version. I have also found this free tool here http://www.mitec.cz/adoq.html http://scintilla.sourceforge.net/SciTE.html
Did not have time to test it but it looks interesting.

 

[CyberLynx]

In your case, because there was a Sample DB to deal with, I created the Query Statements directly within the VBA Editor.

If you would like to send a striped version of your DB with some fictitious data then I will gladly take a look at it to see what the problem is. Just send me a PM.

.

 

[smile]

Thanks, I am looking for another approach that does not need to changing SQL code if new field is added or removed from table that stores multiple records.

I don't know how to do this. I trie to use
http://support.microsoft.com/kb/88670

It works but need to keep pushing the button x times you need your records entered.

I also thought that I used module Auto_Fill_New_Record. It duplicates records form last entered. It's a module therefore no complex SQL statements need asjustment if a new field is added or removed to the table later etc.

I made a test database - it works but it needs to be changed that:

How I need it to work:

A user opens the form and pushes "add new record button"
Blank empty record is shown to user
User enters information in the fields

If
user pushes button "save record" or "closes form" - include records 1 time as normal

if user pushes button "copy" then multiple records a added. Textbox "copies" controls how many records to enter.

I'm unsure if it's better to do like I say above or to have this scenario:

if user pushes button "copy" and in textbox "copy" there is 1 - include records 1 time as normal
if there is more than 1 - multiple records a added

I would like to give use pop up box. Indicating that multiple records will be added. And yes no buttons so it would be possible to stop if the button was clicked by accident.


I have made a sample db please take a look.
I have created tbl_items2 form and added some test data.

 

[CyberLynx]

OK...so you want to keep it simple.

Well, for duplicating a record, MS-Access can't make it any easier. Just place a new Command button on the Form when in Design View then allow the button wizard do it for you under Record Operations | Duplicate Record.

Based on this principle I have modified your last DB sample to utilize the method used my Access except using the RunCommand method instead of the DoMenuItem method which is now out of date anyways.

Attached to this post is your latest DB Sample modified to accomplish what you want. See the code underlying the Copy button within the OnClick event. Code is well commented so that you can see what does what.

Again, as with any code I have provided, you can modify it to suit your specific needs if required.

The Form the new code was applied to is the tbl_items2 Form ( I wish you would prefix your Form names with frm instead of tbl)

If you like what you have then you can get rid of that Code from MS. I did not use it.

.
.

 

[smile]

Hi, thanks for your reply. It's working but if I enter 5 records it gives me 6. It always gives one record too much.

I changed the code here to make it subtract 1 from entered value and add 1 on msg box. This is my try at coding so I thought I ask if I have made any mistakes etc.

'If the Cnt variable contains 0 (number entered was 0) then exit
   'this procedure
   If Cnt = 0 Then Exit Sub
   'If the Cnt variable contains > than 0 then -1 from value
   If Cnt > 0 Then
   Cnt = Cnt - 1
   End If
   
   'Prompt to ensure this action is truely desired. If not then we
   'exit this procedure.
   If MsgBox("Are you sure you want to make " & Cnt + 1 & " copies" & vbCr & _
             "this Record?", vbQuestion + vbYesNo, "Are You Sure?") = vbNo Then Exit Sub

 

[CyberLynx]

There is something very odd about your Database. A For/Next loop in one procedure outputs to little and a For/Next loop in another procedure outputs to much. For some reason, I think the no matter what loop mechanism we use it's going to be different.

Does the Modified Sample DB I sent back to you do this? I ask because on my system, the sample works just fine. If it does work fine then you need to find out why your loops are anding or subtracting counts. You may be having this problem on your current computer system but if you move the database onto a different computer system then it may work fine. If you have made changes to the count mechanism then it may work fine on your computer but fail miserably on other computers.

When a record is entered into the Form it (1 record) is saved to Table since the Form is Bound to Table and the fields contained therein. There is now one record in Table of the particular item. If you remain on his particular item and now decide that you want to make 5 more copies of it then you would enter the number 5 into the copies Text Box. You hit the Copy button then 5 more copies of the same Record are saved to Table (1 + 5 = 6). If you were to look at the Table you would see six records additional records. The first Record is your initial Record and the remaining 5 Records are the copies.

If however, you were to navigate to a completely different Record (one that already exists) and then apply the number 5 to the copies Text Box, and hit the Copy button then 5 copies of that Record is saved to Table. When you look at the Table directly after this action, you will see 5 copies of that particular Record.

In essence...you really don't need the Save Record button since the Table and it's Fields contained therein are all bound to your Form. If you want to force the User to utilize the Save Record button then you will need to either:

- Unbind the Form Controls from Table Fields

- Or set up a Form Wide Variable to act as a Flag when the Save Record Button is hit and if the User tries to either close the Form or Navigate away from the Record entered or modified and this Flag is not set then the User is prompted (Yes/No) that the record was not Saved and do they want to save it. If the User enters no then the Data they had entered is Undone.

In my opinion it's better in most cases to work with Unbound Forms or Form Controls. It gives you control of the Form in relation to the Table(s) instead of MS-Access doing it but be prepared to code for it.

Again, because your Form and their Controls are Bound to a Table, the Add Record button really isn't required either since your Form does contain a Navigation Bar. A new Record can be added by simply selecting the New Record (>*) button on the Navigation Bar or by navigating to the last record and then select the Next Record (>) button. It is harmless as it is and the way you have it, it makes it easier for a User to understand what to do in order to add a record.

Bottom line....your code will work fine. Modifying the code to work for you is the name of the game. I would of however done it a slightly different way:

For i = 1 To Cnt - 1

But do keep in mind what was mentioned above.

.

 

[smile]

Thanks for your prompt reply. If you say that the VBA works by:

1. Save record to table
2. copy record number times in textbox

Then I think there was a slight misunderstanding.
You misunderstood me how I need it to work, and I did not understand why it gives me 1 record too much.

I wanted the form copy button to enter exact number of records entered into text box. Not make a copy record button the name of the button is confusing, sorry.

In your original database if I enter 1 I get 2 records.
In my database with (-1 record code) I get 1 as Access adds one record to db and since we subtract 1 it never makes a copy.

------------------------------------------------
So if a user enters 0 it does not run - this is OK
If a user enters 1 - form enters 1 record.
If a user enters 2, 5 10 etc. then 2,5,10 records are entered.

Where should I use this line, is it better than my IF statement?

For i = 1 To Cnt - 1

 

[jai kushwaha]

hello friend i have a field "Operator name"on my form and i want to copy and paste it into next record when i click a button. how can i do it??

i am stuck please help.........

thanks in advanced.........