Linking Append Query To A Command Button

[MCatz]

Hello (Access) World,

I'm having some trouble figuring out how to use the append query with a command button on a form. Basically, all I'm trying to do is fill out all of the fields on the form and have them populate into the master table.

I figure this is something simple, but I've just recently started learning Access (4 days strong!), so it's flying over my head.

 

[John Big Booty]

First up start by constructing a select query that collects the data from your form. You can do this by putting the following in the top row of the query builder grid;

 forms!YourFormNamer!YourControlName

This will show in the top row as;

Expr1:[forms]![YourFormNamer]![YourControlName]

You can change the Expr1 to a more meaningful name if you wish.

Once you are happy that the query is collecting all the relevant data, convert it to an Append Query, appending each Expression to it's relevant field within the table.

 

[MCatz]

Hey John, thanks for the quick response.

Just for clarity, I'm supposed to add that expression to each field? I attached a picture, so you could see what I did.

If that's correct, how do I add that to the OnClick Event of the button on the form or do I even add it to the OnClick Event?

 

[MCatz]

Alright...I think I got it working as it's supposed to.

A couple of other questions:

1) I have one combobox that only appears when a "No" checkbox is checked. It works fine if you've checked the "No" box and fill in the next field. I can't get it to work if I check "Yes" and don't fill in the combobox that appears. It gives me a "You can't go to the specified record." How can I make that field return a blank to the table if nothing is entered?

2) Also, is there anyway to put the master table within the form, so the user can see that it is appending to the table after he/she hits the "Add Record" button? I've tried fooling around with sub-forms but can't get it to work.

Here's an update of my database thus far.

 

[boblarson]

Just a quick question -

Why have you put a clock on the front of the form? I know it seems like a good idea but, in reality, it

1. means a timer is continuously running which takes some cpu time
2. is redundant because everyone most likely has the date/time in their task manager.

Personally, I know the temptation to put that there is great and I succumbed to it many, many years ago when I first started with Access databases and then I came to the conclusion that it was really not necessary. And also it is a pain if you have the form in form view and you are trying to do any coding anywhere else - the form timer will cause your cursor to skip backwards, spaces to close, etc while you are trying to code and it is a pain in the backside. You always have to remember to put the form in design view or have it closed when you are working on coding or else it gets really annoying.

 

[MCatz]

Hey Bob,

No real reason for the clock. I was skimming various websites for tutorials, saw the clock one, and decided to give it a go.

I'm still in the "little kid" phase of Access...any little function I can actually get to work amazes me...

Any ideas on my quandary?

 

[boblarson]

Hey Bob,

No real reason for the clock. I was skimming various websites for tutorials, saw the clock one, and decided to give it a go.

I'm still in the "little kid" phase of Access...any little function I can actually get to work amazes me...

Any ideas on my quandary?

For the Further Problems item - you need to go into your relationships and uncheck Referential Integrity on the link between the EqupmentID table and the problems table. Then the error won't happen. But, you still need to handle the null combo by using code in the form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Len(Me.cmbFurtherProblems & vbNullString) = 0  And Me.OptPrecision = 0 Then
      Msgbox "If you choose No for Precision State then you need to include the Further problem", vbExclamation, "Error"
      Cancel = True
   End If
End Sub

 

[boblarson]

2) Also, is there anyway to put the master table within the form, so the user can see that it is appending to the table after he/she hits the "Add Record" button? I've tried fooling around with sub-forms but can't get it to work.

Use a listbox with a rowsource pulling from the table.

 

[MCatz]

For the Further Problems item - you need to go into your relationships and uncheck Referential Integrity on the link between the EqupmentID table and the problems table. Then the error won't happen. But, you still need to handle the null combo by using code in the form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Len(Me.cmbFurtherProblems & vbNullString) = 0  And Me.OptPrecision = 0 Then
      Msgbox "If you choose No for Precision State then you need to include the Further problem", vbExclamation, "Error"
      Cancel = True
   End If
End Sub

Hmm....I deleted the Referential Integrity between the two tables and inserted that code into the Form's BeforeUpdate Event, but it came back with the error message "Method or data member not found" pointing to the .cmbFurtherProblems. Not sure why that's happening as that's what its name is.

EDIT: Never mind, it was just misnamed. Should've been .cmbProblems.

 

[MCatz]

Use a listbox with a rowsource pulling from the table.

Sorry Bob, I got a little confused by what I'm trying to do here.

Is that all done within the wizard or are there more steps involved?

I also noticed that the listbox returns numerical values for the months, area, year complete, and further problems. I assume it has something to do with the way the tables are linked....??

Thanks for the help thus far though .

 

[boblarson]

Your description told me immediately that you used LOOKUPS in fields at table level. It is not good to do so. See here for why.

Remove the lookups at table level and use lookups only on forms. See here for how to remove them.

 

[MCatz]

Alright, all of the LOOKUPS have been converted to Text Boxes.

One last thing...I've got the subform on the table that shows the Master Table, but it won't update automatically unless I manually open the Master Table.

Is there a way to make it do that?

 

[boblarson]

One last thing...I've got the subform on the table that shows the Master Table,

???? Do you mean you have the subform on the FORM which is bound to the master table?

but it won't update automatically unless I manually open the Master Table.

If you have the subform on the main form and when selecting the subform control (control on the parent form which HOUSES the subform) and going to the properties and on the DATA tab the MASTER and CHILD links must be set to the correct PK/FK combination. The Master link is for the field in the parent form which would link the child table and the Child link is the link (FK) in the child table which would link to the PK field in the parent table.

 

[MCatz]

???? Do you mean you have the subform on the FORM which is bound to the master table?

Sorry, you're right. I did mean a subform on a form.

If you have the subform on the main form and when selecting the subform control (control on the parent form which HOUSES the subform) and going to the properties and on the DATA tab the MASTER and CHILD links must be set to the correct PK/FK combination. The Master link is for the field in the parent form which would link the child table and the Child link is the link (FK) in the child table which would link to the PK field in the parent table.

I'm not sure exactly what the correct PK/FK combination is.

I'll describe my steps and perhaps you can troubleshoot what I may be doing wrong:

1) Create Sub-Form with Wizard
2) Use existing Tables and Queries
3) Select "Table: EquipmentID_Table" and select all available fields
4)

Since I can't see what each option is, I just pick None for the time being

5) Name it
6) Right-Click on subform > Data Tab > Not sure what should be in the Master Fields column or in the Child Fields column

Here's a copy of the database as is right now

 

[MCatz]

Any ideas?

 

[boblarson]

For the main form, you don't want any links because the subform's recordsource is the same as the main form's recordsource. But how you get it to update in the subform when the main form is updated is to use a requery on the MAIN form's AFTER UPDATE event:

Private Sub Form_AfterUpdate()
    Me.EquipmentID_Table_subform1.Requery
End Sub

 

[MCatz]

Thanks Bob...worked like a charm!

One last thing, and I'll stop asking questions for a bit.

For some reason, the month, year, area, and further problems continue to show up as numbers (corresponding to each's autonumber in their respective tables). How can I get them to show up as the text input and not the number Autonumber input?

I've tried creating relationships between the two tables (and Enforcing Referential Integrity) to no avail.

What else am I missing here?

 

[boblarson]

Not sure what you mean because I don't see autonumbers for those. What form are you talking about? In fact, in the Equipment_ID table fields there are text which some should be numbers.

Also, why have a months table and a years table?

 

[MCatz]

The ones that don't have autonumbers filled in are ones I filled in manually at the table level.

When I try to use the "Add Record" button (after applying the "After Update" Event code you gave me to the form), it converts the fields with dropdown text into their corresponding autonumbers.

Ultimately, I'd like to have each one of those fields searchable (as you can see in my other form from the "Main Menu") so you can see all precision work done for a certain year, a certain month, a certain area, etc. or any combination of the fields.

Here's an updated version of what I'm working on now.

 

[boblarson]

I see. Well, believe it or not, that is what it SHOULD be doing. So you can either, in the subform's recordsource, use a query for its recordsource which includes the other tables and uses their descriptions instead of their ID's or you can, since this is already like this, just change the bound column on the combos to 2 instead of 1.

But personally, I would

1. Redesign the tables so it uses numbers for all of the keys (PK and FK).

2. Stores the KEYS instead of the text.

3. Use queries to display the text while keeping the ID's as the stored item.