Automatically Create Records?

[andmunn]

Hi All,
I think i'm officially stumped - have tried a few things, but i'm pretty sure this involves some coding.

To begin i'm creating an audit database (auditing issues around the organization). I've "layered" my database in such a way that i have the following tables:

tblAudit (the name / date / etc of the audit)
tblTest (the item's i'm testing in the audit - usually betwene 8-10 items require testing).
tblAttributes (the various attributes I check for each test).

What i want to happen is that when a new "Test / Sample" is created (i.e.// we usually perform 25 tests per audit), the attributes are automatically loaded from tblattributes even if they are nil (but they will never be nill in the end).

I.E//
tblAttributes includes:
> Type of "Attribute" being tested (i.e.// Was proper managemetn oversight completed)
> yes / no field (was this "attribute done)")
> comments

So, when i create a test incident, it will automatically load all the various attributes which i have to "test" so i can simply choose yes / no, and add any comments as required.

Hopefully this makes sense - i'm very stumped here.

Andrew.

 

[CraigDolphin]

The general approach you will likely need to take is going to involve using vba code in your data entry form to run an append query that will add the relevant list of Attributes to tblAttributes whenever you select a new item to test on your form as part of your audit (you will lilkely then need to refresh or requery the form's rowsource to show the new data in the table).

However, the query will need to be told what attributes should be loaded by default for each of the items. This will require you to create a default/setup table that will store that information. e.g.,

tblItemAttributeDefaults
ItemAttributeDefaultID (pk, auto)
ItemID (fk)
AttributeID (fk)

If you use this general approach, you will also likely need to consider how to handle situations where the user selects and item to audit (which triggers the append query to run and add records) and then changes his mind (say he picked the wrong item by accident) and changes the item. You'll likely want to delete the records you just created and then re-run your append query using the new item id.

An alternative approach would be to do much the same thing but instead of placing the new records in the main table, place them into a temporary table and then, after the user has finalized the values transfer the records to the main table.

 

[andmunn]

Thanks Craig - greatly appreciated.

I will give the above a shot and see if i can figure this out

Andrew.

 

[andmunn]

Hi Craig,

Been experimenting with this - and can't seem to get it work. I basically created a command button (Add Attributes), which will run the query "qryAttributesAppend".

>qryAttributesAppend
>fkAttributeID
>fkProcessID ([Forms]![frmTest]![fkProcessID])

Basically, i'm trying to get it to append the attributes (fkAttributeID) according to which process we are selecting. I get an error that states:

"Can't append all the record in the append query". The database set 0 field(s) to Null due to a type conversion failure, and it didn't add 7 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations. Do yo uwatn to run the action query anyway?".

If i say yes - nothign get's added to my "tblAttribute"...

Very stumped.

Thanks,
Anrew.

 

[boblarson]

What is the SQL for the query you are trying to use?

 

[andmunn]

Hi BOb,

Here's the code:

INSERT INTO tblAttribute ( fkAttributeID )
SELECT tlkpAttributes.pkAttributeID
FROM tlkpAttributes
WHERE (((tlkpAttributes.fkProcessID)=[Forms]![frmTest]![fkProcessID]));

Thanks!
Andrew.

 

[boblarson]

So, in tblAttribute, fkAttributeID is NOT set as a PK, correct? Is there a primary key on that table? If so, is it an Autonumber?

Also, make sure that the form reference is actually providing the data you think. It could be that it isn't and so the result is null for the query and so it can't insert a null record.

 

[andmunn]

Hi Bob,

In tblAttribute, pkAttributeTestID is the Primary Key and is an autonumber, and fkAttributeID is a foreign key within tblAttribute (links to table tlkpAttribute).

When i run my "append" query as a select query, i get the query to pop up like i want: I.E// it pops up with a list of related attributes. But when i try to change this to an append query, is when the mistake happens..

Hm...
Andrew.

 

[boblarson]

The key violation part just keeps jumping out at me that seems like it is trying to put duplicate data in without some sort of ID to go with it. So is that the full SQL that you are using? I would think you would also be inserting an ID for the proccess ID as well.

 

[andmunn]

Hi Bob,

I think i figured it out - i made sure to append a value to each field in "tblattributes":

T INTO tblAttribute ( fkAttributeID, fkSampleID, fkRatingID )
SELECT tlkpAttributes.pkAttributeID, Forms!frmSample!pkSampleID AS Expr2, 3 AS Expr1
FROM tlkpAttributes
WHERE (((tlkpAttributes.fkProcessID)=[Forms]![frmTest]![fkProcessID]

I don't get the error now. I also added a requery to the command button such that these new records are immediately displayed.

Is there any way i can prevent this "append query" from runing more then once for each SampleID? I.E>// each sample ID should only have 1 set of associaetd attributes..

Andrew.

 

[boblarson]

You can use a DCount to see if there are more than 0 records with that ID and if not run the append query and if so, bypass it.

 

[andmunn]

Thanks for your help bob - i'm familiar with DCount operator, and this seems to do the trick:

=DCount("[pkAttributeTestID]","tblAttribute","[fkSampleID]=forms!frmSample!PkSampleID")

It returns the number of attributes assocaited with that test item. I, however, do not know how to write the if statement. I want it to look something like this:

If
DCount > 0 then Do Nothing
If DCount = 0 then run qryAttributeAppend
end.

I think, doing it this way, it would be possible to put it on the "on open" event of the form, rather then have a seperate command button, correct?

Andrew.

 

[boblarson]

If
DCount > 0 then Do Nothing
If DCount = 0 then run qryAttributeAppend
end.

If DCount("[pkAttributeTestID]","tblAttribute","[fkSampleID]=forms!frmSample!PkSampleID") = 0 Then
   ' run query here
End If

No ELSE is needed here.

 

[andmunn]

Thanks Bob for your help - makes perfect sense. I feel like my access knowledge is growing daily! (albeit slowly).

I decided to add this as a command button - but notice one thing. If i'm on my main form (frmsample) and i'm working on a brand new "sample", and I click the "create attributes" button, they aren't created unless i've chosen a field that is part of the subform (i.e.// the main form frmSample needs to "save" first to tblSample). How can i force frmSample to save immediately apon opening so that i can create these attributes?

If i'm coming back on a previousyl create dsample, it works fine because the data is already saved in tblSample.

I have no idea if this makes sense
Andrew.

 

[CraigDolphin]

try using something like

Me.Dirty = False

... in your button's on_click event before the rest of your code runs. That forces the current record to save the data to the table. Ordinarily, the data in your form is saved to tables whenever you change records, or the form loses focus.

 

[andmunn]

Craig- thanks so much - did the trick perfectly. It all works as intended now - thanks so much to all.

Andrew.