Add record button

[brocsman]

Now I know this is going to sound daft, but I really can't figure it out.

I've designed my form based on a table I want to populate, using data brought in from other tables and queries as lookups. This all seems to work.

I've now put a button on the form, on click, to add a new record. I switch to Form View, and run through the form, choose my data, and press the button. Nothing happens. No error message, no record added to my table. What am I doing wrong?

 

[John Big Booty]

You are aware that the Add New Record button is only taking you to a blank record so that you can enter new data into a New Record? Perhaps you where hoping for something different

 

[missinglinq]

As John has suggested, it sounds like you're changing data on an existing record and then clicking your button, expecting it to save a new record, with the new data, as you would when using "Save As" in MS Word or Excel, and that's not how Access works.

As John said, the Add New Record button generated by Access moves you to a new record, then you enter your data, via the keyboard or using comboboxes or DLookups, and the new record is daved when you move to a different record or close your form.

Linq ;0)>

 

[brocsman]

You are aware that the Add New Record button is only taking you to a blank record so that you can enter new data into a New Record? Perhaps you where hoping for something different

Sorry about this. I told you I hadn't done it for fifteen years. I want my button to append the data collected in the form to my underlying table. I thought setting the"Data Entry" property to Yes would append a new record to the table, and the button would then populate that record. What do I need to do to make this happen?

 

[John Big Booty]

Setting the Data Entry property of the form to Yes means that the form will always open with existing records filtered out.

Now are we talking about a bound or unbound form?

 

[missinglinq]

It does, indeed, sound like an unbound form, John. The fact that nothing happens when the Add Record button is clicked and no error is popped tends to back this up, as that is exactly what happens in this scenario.

 

[brocsman]

OK, it looks like I've forgotten everything in the last fifteen years. Here's what I'm trying to do.

I'm setting up a database to gather records about wildlife. Puting it simply I want to store data about what is found, who found it, when and where. There's more, but that's it in a nutshell.

I've set up tables of wildlife names, people and locations, and another table where these are brought together to store what was found, who by and where.

I am using a form to gather the information, with combo boxes for each item, which I want to display the list from the people table say for the user to choose, and when the record is complete, the key for the relevant value chosen in each combo box being appended as a single new record to the record table. I thought the Add New Record button would do this. It doesn't.

What have I done wrong, and how do I fix it?

Thanks for your help so far.

 

[missinglinq]

In your form's Design View

1. Select the Form itself and go to Properties - Data and set the RecordSource to your main (record) Table
2. Select a Combobox, go to Properties - Data and set the ControlSource to the field in the main Table that will hold that piece of data
3. Repeat Step 2 for each Combobox

Now, when you click on your Add Record button, you'll be presented with a new, blank record. You make the appropriate selection from each Combobox and when the record is saved (by moving to another record or simply closing the form) the record will be saved with the data selected.

As you move from existing record to existing record, the Comboboxes will reflect the selections for that particular record.

Linq ;0)>

 

[brocsman]

OK. Great. Thanks for that. I've now got a working form that displays the right values from my static data tables and stores them as keys in my underlying records table.

As you say the record is written to the table when I close the form, but I want to do this using a button, which stores the record, then clears the values from the form so that another record can be input. I also want a second button for use when all data input is completed, which will return me to my menu (which I haven't created yet). How do I do that?

 

[missinglinq]

...I want to do this using a button, which stores the record, then clears the values from the form so that another record can be input.

Stop and think about what you're saying. You want another button to go to a new, blank record. That's exactly what the Add Record Button you already have does! And as stated, when you move to another record the current record is saved.

...I also want a second button for use when all data input is completed, which will return me to my menu

Create a button, using the Wizard, and place this code in its OnClick event:

If Me.Dirty Then Me.Dirty = False
DoCmd.Close
DoCmd.OpenForm "MenuForm"

replacing "MenuForm" with the actual name of your menu form.

Linq ;0)>

 

[brocsman]

Yes, absolutely right again, but my form doesn't clear. How do I set it to display with all fields vacant?

Stop and think about what you're saying. You want another button to go to a new, blank record. That's exactly what the Add Record Button you already have does! And as stated, when you move to another record the current record is saved.

Linq ;0)>

 

[missinglinq]

If by "my form doesn't clear" you mean that the comboboxes are showing the last selections made in them, you've omitted one or more of the steps given above, possibly #2, which needs to be done for each combobox. You need to go back and step thru the instructions again.

 

[brocsman]

OK. It all works now. Many thanks for getting me through. Watch this space for more problems when I start trying to produce reports!

 

[missinglinq]

Glad we could help!

Linq ;0)>

 

[brocsman]

One more question before I put this form to bed. One of the data items is a location, say the New Forest. I can pick this up now from the list offered by the combo box, but for any date the likelihood is that all the records will have come from the same location.

How can I set the combo box to treat the first chosen value as its default until a different one is chosen. I can think of two possibilities, and can't do either of them.

Either display the list starting at the last chosen value, or set the combo box to NOT clear when a new record is added, and just tab past it. I think I prefer the latter, but either would do.

Thanks again.

 

[missinglinq]

Once a selection is made, that selection will be the DefaultValue until you either make a new selection or close the form.

Private Sub YourComboboxName_AfterUpdate()
   Me.YourComboboxName.DefaultValue = """" & Me.YourComboboxName.Value & """"
End Sub

 

[brocsman]

I'm back! My form works a treat, many thanks. I'm on data now.

As I said at the beginning I'm reconstructing a database with non-normalised tables, but there is a lot of data, and I don't want to have to re-enter it all.

Simply stated, my problem is this. A Dandelion eg is a member of the Compositae Family. I have a table which shows both of these values as names (this contains about 3500 different entries). I have another table which shows only the Family (there are about 250 of these). I need to pick up the relevant value from the first table, and search the second table until it finds a match, then replace the name in the first table with the tuple number (ie the key) from the second table, or append it as an additional data item temporarily, but it would take weeks to do it manually.

I think I could do it using a filtered query, but I wouldn't know how to automate it, and that would be time-consuming too.

What's the quickest way please?

 

[John Big Booty]

First up you need to develop the structure of you new database. Then from there pull out your highest level classification and create a table for it, that contains an ID and the various subdivisions within that classification. You should be able to do this with a group by query. At this point you will be able to identify typographic and input discrepancies, such as Normalisation v. Normalization and correct those discrepancies. Once you've done that you can then use an update query to update your Text values to ID numbers. You can continue in this manner until you have reached you normalised table structure.

 

[brocsman]

Thanks again. All normalised now, and my static data is in place. I want to make one change to my form.

I want to include a combo box that is only visible if the value of one of the the data items in the underlying table of another combo box is set to Yes, so I'm thinking I need a macro linked to AfterUpdate for that Combo Box.

I also need to reset the visible/invisible Combo Box to not visible after update.

 

[John Big Booty]

Thanks again. All normalised now, and my static data is in place. I want to make one change to my form.

I want to include a combo box that is only visible if the value of one of the the data items in the underlying table of another combo box is set to Yes, so I'm thinking I need a macro linked to AfterUpdate for that Combo Box.

I also need to reset the visible/invisible Combo Box to not visible after update.

You could use something along the lines of;

If Me.combo1.Column([B][COLOR="Purple"]X[/COLOR][/B]) = "Yes" Then
     Me.Combo2.Visible = True
Else
     Me.Combo2.Visible = False
End IF

In both the On Current Event of the form and the After Update Event of the combo.

It will just be a matter of replacing X with the appropriate column number, remembering that the columns are numbered from zero up.