Save to New Row

[adam]

Hi,

I'm having a Access user form where I have embedded a save button. The save button adds the first record to the first row the data sheet from where the form was created. But when I enter a second customer Information, The button adds it to the same row instead of a new row.

I've set the primary key as text Which Is the Customer ID.

The customer ID format that I'm trying to use is as C 123456 and so on.

How could I overcome this?

[Wendell]

I'm afraid we need more information to help you - this appears to be your first post in this forum, so we don't have any background on your application. It would be best if you could attach a simplified and sanitized version of your Access database to a post so we could see the code behind the form as well as the table structure. As a general comment, Access forms don't typically need a Save button unless the form is unbound, or unless you have modified the tab order property of the form so it doesn't go to a new record after the last data element has been entered.

[adam]

To detail my question furthermore, I would like to mention that I do assign Unique Customer ID’s for each customer but in different formats.

Some would have A 123456 Others WV 134567 and some as H 125468.
The numbers are assigned depending on the insurance card number they provide during registration.

If this is the situation would the data get added to individual rows with the save button?

[HansV]

Microsoft Access works a bit differently than other applications - it'd be useful to follow a course in database design using Access, or study a book.

As Wendell wrote, there is no need for a "Save" button. Access will save the record automatically when you move to another record or close the form.

You should make the navigation buttons visible, or provide a "New Record" button. When the user has finished entering the data for a customer, they can go to a new record. The screenshot below shows the New (blank) record button in the navigation buttons.

x261.jpg

Another option would be to set the Data Entry property of the form to Yes. It can then only be used to enter new records, not to view or edit existing records. Each time the user opens the form, it will display a blank new record.

[adam]

Thanks for the reply. Hans. Like you've pointed out, Microsoft Access works a bit differently than other applications.

So I though it'd be useful to know what I have done wrong in my user form that is preventing me to add data to unique rows.

With your reply, now I've cleared the matter that it'd be useful to follow a course in database design using Access, or study a book.

[adam]

By the way, the problem in Post 24137 is solved. But why doesn't the formula put the age in the column "Age" of the data sheet when it puts calculated age in the text box "Age"?

[HansV]

A calculated control doesn't store its value in a field.

You don't need the field Age in the Customers table. One of the "rules" for good database design is that you should not store calculated values in a table, since it is derived information. It is superfluous to store derived information.

(There is an exception to this: if the calculations become to slow, it may be better to store their result, but this only happens with extremely large tables and/or very complicated calculations)

[adam]

What might be the alternative for this? as age is important in this situation.

[HansV]

You could create a query based on the table, with Age as a calculated column. Use the query as record source for the form.

[adam]

Based upon your reply, I've created a query from the Customers table and a form from the query table.

My question of concern is how could I make the age appear as;

Let's say, for example if the date is 17/1/1999, I want the age to be as 11 years.
if the date is 17/1/1999, I want the age to be as 6 Months.

[HansV]

Let's say, for example if the date is 17/1/1999, I want the age to be as 11 years.
if the date is 17/1/1999, I want the age to be as 6 Months.

Huh?

[adam]

Sorry it was a mistake. The second date would be as 17/1/2010. which means the age should be as 6 Months.

[HansV]

See the attached version. I modified both the module with the code and the query.

Note: it is a good idea to compact a database before zipping and attaching it. To do so, click the Office button, then click Manage > Compact and Repair Database. This will reduce the size of the database significantly, especially if you have edited it.

DateQuery.zip

[adam]

Thanks for the help Hans. I'll follow what you've said when attaching the database sheets.

Meanwhile,I've missed out one more line. That is to make the age even appear as days.
For example, if date of birth is 31/7/2010 then the age would be as 2days. How will I achieve this?

When the first number or any number is written in the date of birth text box, the age column shows 0 until the complete date is written. How could this be changed so that the age appears after the tab button when the user writes date in the date of birth text box.

[HansV]

Once again, see the attached file. This time, only the code module has been changed.

DateQuery.zip

[adam]

Thanks for the help, Hans. The sample works magnificent.

But why doesn't the following code embedded behind a command button clear all the data from the active form?

Code: Select all

Private Sub cmdClear_Click()
Dim ctl As Control
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If ctl.ControlSource = "" Then
ctl.Value = Null
End If
Case Else
End Select
Next ctl
End Sub

[HansV]

Because of the line

If ctl.ControlSource = "" Then

the code will only clear unbound controls (controls that do not display a field from the table/query).

Why do you want to clear all controls?

[adam]

My intention is to clear the misspelled fields.

Lets say for example If I write the Customer Name As Adman Instead of Adam or less and after entering all the fields if I discover the data has been already entered I want to clear all the fields and start typing with new data.

So, do you mean to make the code work, I have to add the line Application.CutCopyMode = False ?

[HansV]

I'm sorry Adam, I pasted the wrong line into my previous reply. Instead of

Application.CutCopyMode = False

it should have been

If ctl.ControlSource = "" Then

Try removing that line and the corresponding End If. You don't need the line

Case Else

either, since there is nothing between Case Else and End Select.

[adam]

Thanks for the help Hans.