Very basic form question

[chancer1967]

Apologies for the low level of this query, but im just starting out!


I have a table of customer details - customer ID, customer name, address, telephone, email, etc

The customer ID is the primary key and is non sequential


I am creating a form to update customer details, currently it is all text boxes.

For existing customers, i want the customer ID field to be locked, as it is way too easy for a user to overtype it thinking it will make them jump to another record. The other fields are all unlocked.

But if the user wants to add a new customer, i want them to be able to click on the new record button and for the customer ID field to now be unlocked.

So i want customer ID to be locked if it is an existing record and unlocked for a new record. Make sense?


Thanks

 

[ColinEssex]

Use another IDNo that is an autonumber and don't include it in the form. Use that as the primary key. Then you don't need to worry about the other CustomerID being tampered with and screwing things up.

Col

 

[Mile-O]

I have a table of customer details - customer ID, customer name, address, telephone, email, etc

I'd still use an autonumber for the primary key and have a field for youe entered key [just Index it: Yes (No Duplicates)]

So i want customer ID to be locked if it is an existing record and unlocked for a new record. Make sense?

In the form's current event, select the Code Builder and type:

If Me.NewRecord = True Then
    Me.txtCustomerID.Enabled = True
    Me.txtCustomerID.Locked = False
Else
    Me.txtCustomerID.Enabled = False
    Me.txtCustomerID.Locked = True
End If

Replace txtCustomerID with the name of your textbox.




Also, if the customer is a person then I'd advise breaking their name down into forename and surname.

 

[Rich]

Private Sub Form_Current()
If Not Me.NewRecord Then
Me.SomeField.Locked = True
Else
Me.SomeField.Locked = False

End If
End Sub

 

[chancer1967]

awesome


so here are the follow-up questions


1. the customer table is in alphabetical order. when a new customer is added i want the table to re-sorted to keep the order correct. im guessing its a simple piece of code...

2. id rather ditch the record navigation buttons completely and have a list box for all of the customers, and a 'new' button next to it for when a new record needs creating. a record delete button would also be neat. are these simple things to do?


many thanks

 

[Mile-O]

1. the customer table is in alphabetical order. when a new customer is added i want the table to re-sorted to keep the order correct. im guessing its a simple piece of code...

Simpler than that - tables are unsorted sets of data. You should base a query off your table, ordering the records however you want, and then setting the query as your form's RecordSource and not the table.

2. id rather ditch the record navigation buttons completely and have a list box for all of the customers, and a 'new' button next to it for when a new record needs creating. a record delete button would also be neat. are these simple things to do?

Yes. For the New, all you need to do is open the form like so:

DoCmd.OpenForm "MyForm", , , , acFormAdd

For the delete you should just make a Delete Query, and open/run it when you want to delete a record.

 

[chancer1967]

You should base a query off your table, ordering the records however you want, and then setting the query as your form's RecordSource and not the table.

i presumably need to refresh the query each time the form changes any records