New record with similar data

[james_IT]

Hi

I have a stock items in the database that I want to duplicate over and over (all but a few fields are the same e.g. same thing but different size and price)

I want to a command buttong to open a new record in the "add new" form (frmTicketing) and enter the stock item with the same information bar some fields. So far i have:

Private Sub Command47_Click()
On Error GoTo Err_Command47_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Me.StockNumber = Null
Me.CV_Code = Null
Me.txtLFeet = Null
Me.txtLInches = Null
Me.InvoiceNumber = Null
Me.InvoiceTotal = Null
Me.Salesman = Null


Exit_Command47_Click:
    Exit Sub

Err_Command47_Click:
    MsgBox Err.Description
    Resume Exit_Command47_Click
    
End Sub

 

[pbaldy]

There are a couple of methods here:

http://allenbrowne.com/ser-24.html

 

[missinglinq]

I'm sorry, but what is your question? There are a number of ways to do it, as Paul suggests with his link to Allen Browne's site, but your way should work fine, as well!

 

[ajetrumpet]

this may work too

 

[james_IT]

this may work too

perfect! exactly what i was after...

thanks

 

[pbaldy]

May work...may not. I'm going to go with not.

 

[ajetrumpet]

May work...may not. I'm going to go with not.

it's an example ding dong.

 

[pbaldy]

Why do you feel the need to be rude? Your example doesn't do what's expected, though the methodology is interesting. Why not post one that works, instead of calling me names?

 

[missinglinq]

I have to agree with Paul, the attached db sample doesn't meet the OP's stated goals.

I'm also confused as to why the OP doesn't want to use his original code, which should work just fine!

 

[ajetrumpet]

Why do you feel the need to be rude?

it's called a JOKE paul. and quite frankly, i can't believe you said this. I had the worst day probably ever at work today and I'm still able to joke. But maybe it was just taken the wrong way huh?

Your example doesn't do what's expected, though the methodology is interesting.

what exactly was expected? the person said they wanted to have HAPPEN what my stuff does, although it's not pretty.

Why not post one that works, instead of calling me names?

if it doesn't work, then explain this:

perfect! exactly what i was after...

thanks

 

[pbaldy]

if it doesn't work, then explain this:

Lack of testing. Your code copies data from the first record, not the displayed record.

 

[ajetrumpet]

Lack of testing. Your code copies data from the first record, not the displayed record.

thanks for letting me know paul. if that's true, then i posted something that didn't work. i appreciate it. hopefully, the OP can use it someway somehow.

 

[james_IT]

Lack of testing. Your code copies data from the first record, not the displayed record.

ah, yeh just noticed it - only had one record in the table previously!

Does anyone know how to rectify this or another method?

 

[james_IT]

I have to agree with Paul, the attached db sample doesn't meet the OP's stated goals.

I'm also confused as to why the OP doesn't want to use his original code, which should work just fine!

Its not working . Not sure why

 

[james_IT]

With my orig code I get an error 3022 (would create a duplicate index?). Anyone know how I can fix this?

 

[james_IT]

When i try to use my previous code i get an error saying it would create duplicate index (runtime error 3022). How can i fix it?

 

[pbaldy]

You've been given 3 other methods, two of which it would appear you haven't tried. Adam's method would be fairly easy to fix, and as I mentioned, it was an interesting way to do it. You'd open a recordset on an SQL statement that included a WHERE clause to pick the displayed record from the table instead of using RecordsetClone. You'd have to either open the recordset before going to a new record, or at least grab the value from the key field first, to be used in that SQL statement.

 

[Galaxiom]

When i try to use my previous code i get an error saying it would create duplicate index (runtime error 3022). How can i fix it?

Sounds like you are duplicating the record ID.

However I wouldn't fix the original code.
Those menu commands are deprecated since last century and are only included in Access for legacy applications. They should not be used in new applications.

 

[james_IT]

You've been given 3 other methods, two of which it would appear you haven't tried. Adam's method would be fairly easy to fix, and as I mentioned, it was an interesting way to do it. You'd open a recordset on an SQL statement that included a WHERE clause to pick the displayed record from the table instead of using RecordsetClone. You'd have to either open the recordset before going to a new record, or at least grab the value from the key field first, to be used in that SQL statement.

I have tried them... 1 way gave the same values for ALL new entries, not just the next one. I couldnt get the other one to work (carry current before insert)

I have no idea about SQL statements etc. Is there somewhere that explains how to do what you are talking about?

The third method, suggested by ajetrumpet is kinda what im looking for but only copies the FIRST record and not the LAST record - does anyone know how to change this?

 

[missinglinq]

As I said in the other post you originally tagged this problem onto, and as Galaxiom said, perhaps you are trying to duplicate the StockNumber field, which is to say that you already have a record in which that field is Null.

As I also said, elsewhere, I'd go to the table and quickly scan down to see if that field is empty in one of your records.