Locking Records?

[boblarson]

I don't know why you couldn't get my code to work. I got it to work just fine with your database. The only problem with it was that you had set those 4 labels with the tag as well and they can't be set to locked as there is no lock for a label.

If you use my code, your search will work just fine. When I get to work later today I will post your database with my changes so you can see how it works.

 

[boblarson]

Here's your database with the changes. I also have code in a module that helped me track down the problem.

 

[dalesellers]

boblarson - thank you for taking the time to get it working. I checked it out and it functions perfectly. Just like you said, the search works fine with this coding method. I’m still looking through the code and trying to figure out where I goofed.

For Bob or anyone else – Since we are on the subject of coding individual fields on a form, let me throw another question in – is there a simple way to get the “Duplicate Record” Command Button to duplicate certain fields in a record, but not duplicate other fields? For example, on an invoice, it would be nice to have a customer’s info duplicated, but not the date, sale amount, etc of the invoice. I’ve been searching extensively but haven’t been able to find any help (tutorials, books, etc) on this topic. I appreciate any insights.

Thanks,
Dale

 

[ansentry]

Re Invoicing;
Have a look at the Northwind example that comes with Access.

 

[dalesellers]

John A - I looked and looked but couldn't find a "duplicate record" used in the Northwind Ex. Any other ideas where I might find out how to do this?

Thanks,
Dale

 

[ansentry]

What do you mean by "duplicate records".

What you should be looking at in Northwind is the Orders form. Open it up and go to a new record and then fill it in.

I have database that I created as an example of how to create multiple invoices using a customer table. 1 Customer can have many invoices each invoice can have many purchases. Very much the same as the Northwind one.

 

[dalesellers]

The duplicate record is one of the Command Button options available in the menu. I have my invoices set up where all customer info and the order can be filled in all at once, instead of in two steps as in Northwind. It’s probably a barbaric way of doing things, so I’ll check into designing the form to where it is linked to two tables.

 

[GaryPanic]

duplicatation a client - couple of ways to approach this

Ihave not look at your D/base but i am about to start a project that may have this

Look at it logically - are you going to have multiple invoices going to a set of clients and is their going to be repeat business - yes good
now as you are now aware their are many ways to skin a cat
first you need an accounts table ( no need for extra work here nice and easy ) i presume that you have enter in name and address per record
you need to have a table to store info in - not the end of the work a slight tweak to you working practice is all that is need right lets set the table up
accountid- autonumber
Accountname txt
Accadd1
accAdd2
accAdd3
this should match what your orginal b/ase is set up to

now all you need is a qry to copy the data from one table to another asign this to a button - hey presto a simple way of generating an account table
now on your data entry form have a drop down box that lists from this table
so on a new record cl;ic new record - and check the drop down box if its their select it and get it to populate the right fields (remember that drop down lists start at 0
you can tweak to your hearts content -
now this would not be considered as best practice - but it will work - rather than going and setting up an account then going into another form to do your other stuff -
this now makes your d/base a tage larger - but probably quicker in the long run - it really does depend on how much invoicing is being done
my approach will allow you not to make accounts as well if you get a one off then just enter in the info and don't push the append button ( but for the sake of 1 record -??) - if the D/base gett hugh consider archiving their are lots of info on archiving depending on your tech level - i approach this in a simple step process (as with all my problems - break it down) into steps each of these steps will be ease then group them together - I hardly use any coding if I can avoid it - as I'm not that good at it and i try to use Access functionallity where ever poss - the Gurus'here will dive in a use VB when its smart to do so - but 90% of what you need is do-able without it
as you have found out the Access Guys (& Girls) are happy to exchange info and help - but occasionaly theycan get a bit techie when a simple yes/no solution will do - i rabbit on - i hope that the above might help you or point you in a direction that leads to salvation

G

 

[ansentry]

GaryPanic,

I must disagree with you re duplicating customer information; there is no need for this.

If you want to do Invoicing then what you need is;

Customer Table
CustomerID (PK-AutoNumber)
Name
Address etc.

Invoice Table
InvoiceID (PK – AutoNumber),
fkCustomerID (Foreign Key – Number )
Date
Delivery Instructions
fkSalespersonID (you would need a table for employees)

InvoiceDetails
InvoiceDetailsID(PK AutoNumber)
fkInvoiceID (Foreign Key Number)
Quantity
Item Description (This should come from product table)
........Etc.

Relationships;
1 customer Many Invoices
1 Invoice Many InvoiceDetails
You would also setup relationships for the products and employees (if you use them)

When a new invoice is created the customer full details would show on the invoice form (and report) but only the customerID is recorded in the invoice table in the fkCustomerID field.

The would be the same for the Item Description in the subform, the full details of the item can be displayed (including price) and yet only the foreign key from the product table and the price will be stored in the InvoiceDetails table.

The reason that the price is “copied” is because if you update the pricing in the product table it would change all the pricing in old invoices.

Have a look at the attached screen dump of Relationship from a sample db I created for someone on the web.

As I said in previous post have a look at Northwind, it all there.

Just my 2 cent rave.

I have posted a sample HERE that shows controls being filled in by a combo and then only saving the key.

 

[GaryPanic]

John A is right
The idea way is to set up an account (in a account table ) and have a transaction table of some sort (invoice or products of some sort )

what i was trying to get to is rather than set up the account then go into a seperate form to enter in the invoice etc
straight into the invocie table select an account from a combo box (if the accoutns not set up have a option to stay in the form your in entering the details ( push a button and create the accoutn withour leaving the form your in) then if you need to use the accout again its their in your table - It probably saves 2-3 clicks of a mouse button

but the end result would be a accounts table and an invoice table

 

[dalesellers]

I am definitely going to build a form that has multiple tables after the advice received here. It makes much more sense, although it will take me a while because of the info that I have to record.

In the meantime, I’d like to keep using the current form. I really like the code that boblarson shared above that allows a checkbox feature that locks records unless checked. The only problem is that the code prevents me from using the “Duplicate Record” command button that is on the form.

Does anyone know an easy code fix that would allow both of these features to work at once? I keep trying and trying but I can’t seem to get it to work. Thanks for any insights.

Dale

 

[GaryPanic]

Ok lets look at this after you got the code working all fine and dandy - try
at the end of the code change the properties of this button/control to enabled
so waht in fact you might be doing is locking everything down(whcih is what you were after - but now you locked too much down) so throw another line at the end of the code to enable the controls you require !! but before you do this make a copy of the D/base - just in case it goes tits up

-you hav been getting a lot of info help from a lot of clever It bunnies , (I am not one of them - like to think of myself as a medium level user which is why my simple approach of having a tick box ticking it and having this lock certain field is the route i have taken (I've done it on my build and this keeps it simple- work is complicated enough- I always approached things as simplisitic as possible - then when you get stuck look at the website form here and 90% of the time the problem you have is here in another guise and if its not scream and shout and the Gurus will help, you may get half a dozen views on how to do stuff - and alot depends on how the question is phased but nearly always someone will help

any try the enable option after the last line of code
try if (Controlname) = 1 or yes or whatever you got it set to then buttonname.enble = true
else button.enable = false

you might have to put this on the form properties as well on current and on load