copy and make visable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a barcode database that generates the barcode using some VB code
(gractiously provided by BruceM).

1ST ISSUE: for some reason the duplicate record command button I've created
(using the wizard) won't work and displays this error:

Records that Access was unable to paste have been inserted into a new table
called "paste errors." ....blah, blah, fix the errors and paste into
original table.

However, it doesn't tell me WHY Access was unable to paste the records and
doesn't even provide a help button when this error happens. Any ideas how to
fix this???

2ND ISSUE: I've discovered that I need the textbox that displays the barcode
number to be locked; unless a copy was just made, in which case it would then
need to be edit-able.

Please help, thank you!!
 
something to consider regarding 1ST ISSUE that I've explained below - when I
open the table directly, and copy a row and paste it into the "new
record/blank" row, it works fine. It seems that it's only the button that
doesn't work.

Does that help at all?

Please help, thank you!!!
 
Have you added any fields or changed field attributes since creating
your button? Could you post the code that actually exists behind your
button?

Secondly, you could unlock the textbox with your CopyRecord button:

Me.txtBarCode.Locked = False

But remember, you'll have to have a way to lock it back up. One way
would be to do so on the Exit event but this won't allow your users to
go back and make any changes. To gain better control of your form, you
may need more buttons, (Cancel, Save, Edit, Delete, New) to achieve
what you want, all with code behind them that the wizard will not give
you.
 
I had made changes to the fields, but I recreated the copy button after the
changes were made. In fact, I just now recreated it again, and here's the
code the wizard produced:

Private Sub CopyRecord_Click()
On Error GoTo Err_CopyRecord_Click


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

Exit_CopyRecord_Click:
Exit Sub

Err_CopyRecord_Click:
MsgBox Err.Description
Resume Exit_CopyRecord_Click

End Sub

thanks so much for the help!
 
I think I would use an SQL statement instead. I had some trouble
making your code work for me on a form also, although I'm sure it's
possible.

An SQL statement will be a little more labor intensive to code. Here's
what it should look like:

DoCmd.RunSQL "INSERT INTO tblBarCode Select BarcodeNo, BarCodeID FROM
tblBarCode WHERE ([BarCodeID] = Forms!frmCustomer.BarCodeID)"

I'll break this down for you a little. Don't use the quotes below in
your real statement. Use the syntax listed above.

"DoCmd.RunSQL" is a VBA command to run an SQL statement.

"INSERT INTO" is the opening part for an SQL append statement.

"tblBarCode" is the name of the table you want to insert the record
into (Change it to match yours)

"SELECT BarcodeNo, BarCode ID" This part of the statement selects
which fields you want to copy. Seperate each field with a comma but
don't put a comma after the last one. If you put "tblBarCode.*"
(without the quotes) it will select all fields. (Note: Use the *
method to select fields will cause errors if you are using an
autonumber field or any primary key because it will attempt to copy
everything exactly and will try to insert duplicate records.)

"FROM tblBarCode" is the name of the table you want to copy the record
out of.

"WHERE ([BarCodeID] = Forms!frmBarCode.BarCodeID)" The WHERE part of
the statement is a criteria statement. If you leave it out, this SQL
statement will create copy all existing records. Since you only want
to copy one record, use the where statement to reference the record you
are currently viewing. [BarCodeID] here would be the field name in the
table that is your primary key. Forms!frmBarCode.BarCodeID is a
reference to the BarCode record that you are currently view on your
form. In everyday english the statment is saying this:

(Copy Record (Which one?) The record in my barcode table that has a
BarCodeID (or whatever you use for primary key) that matches the
BarCodeID of the record I'm looking at on my form.

This SQL statement can't actually copy the record from your form. It's
going straight to the table to find a record that matches the one on
your form, if you have your WHERE statement coded correctly.

Good Luck. If you choose to do this, let me know how it works. -HK
 
Sorry. I see one little error in the above post. I once referenced
your form as frmCustomer and the next time as frmBarCode. Whatever the
name of your form is, just make sure you use it. I used arbitrary
names in all my references to fields, tables and forms. -HK
 
Wow, thank you for the plain-english break-down...exactly what I needed.
They've had me on another project last week, but I think I'll be able to
implement this, this week. I will be sure to reply as to the outcome as soon
as I can.

again, thank you so much for the detailed explaination!!
 
Ok, I've stored the SQL as an event procedure for the "on click" event, and
it works as it should - THANK YOU AGAIN!

My problem now is that I need to make this macro happen directly after the
"on click" event happens:

Macro:
1. Beep
2. Set Warnings = no
3. Set Value.Visable = no (hide an unbound, locked field that shows the
barcode #)
4. Set Value.Visable = yes (unhide the bound, unlocked barcode field for
editing)
5. GoToControl (simply land the focus on the barcode # for easy editing)

However, I'm not sure how to make this work. I've tried letting the macro
run the SQL, but the statement is too long. I've tried creating a module (I
have very limited VB knowledge) and use the "RunCode" option to call the
module, but it tells me the table it's trying to request is opened
exclusively by another user (it is not).

Please advise, and thank you again.
 
Back
Top