I'm not sure what to title this thread as, as I don't know where the problem lies.

[Gerdagan]

Hi everybody,

Hope you're all well.

Here is my set-up.

I have two tables:

1. Equipment.
2. Test History.

I have one query:

1. Due for testing.

I have two forms:

1. Master.
2. Test request.

I have a master form, where for each record (from the equipment table) I have a sub datasheet (test history) which lists the test history of that particular item of equipment.

The query allows me to view equipment due for testing. Using this query I have created another form (Test request) which allows me to update the Test History table of the specific item (i.e. test date, next date due, pass/fail, etc), along with the Equipment table (i.e. status, next due date).

The only problem I am having is, that when I do this, I am over writing the existing record on the Test History table.

How do I make Access create a new record in the Test History table so I can keep my existing test history intact.

The Equipment and Test History tables are both linked with a unique ID to the item of equipment.

Sorry if I have posted in the wrong place, and if this has already been answered somewhere else but I didn't really know what to search for so didn't have much luck in finding anything.

Thanks all.
Ged.

 

[GinaWhipp]

Sounds like you might be running an UPDATE query instead of an APPEND query but hard to say for sure... How are you updating the Test History table?

 

[tablepad]

It's choice - not chance - that determines your destiny.

 

[Gerdagan]

Sounds like you might be running an UPDATE query instead of an APPEND query but hard to say for sure... How are you updating the Test History table?

Hi GinaWhipp,

I was updating the Test History table via the Master form. But I created the query (due for testing) and the new form (Test request) to allow me to enter data that way, for a number of records. The new Test Request form is a continuous form that allows me to view a number of records at the same time. Basically I have a check box with an after update event that when checked enters a date and other data, etc.

I have the same set-up with another part of the database, and it works fine. But that is because I only need one entry on that related child data sheet. In this example I need to keep numerous records, but my current set-up is overwriting the existing record. I was wondering if there was some coding I could but in my VBA event for the check box.

But now you mentioned an append query. I'm sorry, I've had no training in databases I'm all self taught and had no idea there were different types of queries. But what I will do now I know what it is I may need to do I can research append queries.

Ged.

 

[Gerdagan]

Hey,

I've taken a look at an append query, but I don't think it's what I am looking for.

What I was thinking was to add a DoCmd. to my event. My current event is below:

Private Sub Check96_AfterUpdate()
If Check96 = True Then
Me.[Status] = "Returned (Pending)"
End If
If Check96 = True Then
Me.[Date received] = Date
End If
End Sub

What I want to do is to add coding that creates a new record in the Test History table, and adds the date received date as today. Can anyone help me with this bit of coding?

Thanks,
Ged.

 

[CBrighton]

If this Test Request form will only be used to add new test requests then you can set the form properties to data entry mode so it only takes new records.

If it's used for both then it may be simplest to just add a command button to move to a new record for users to click before adding a new entry (thus allowing them to view & update previous records and add new ones).

 

[Gerdagan]

If this Test Request form will only be used to add new test requests then you can set the form properties to data entry mode so it only takes new records.

If it's used for both then it may be simplest to just add a command button to move to a new record for users to click before adding a new entry (thus allowing them to view & update previous records and add new ones).

Hey thanks,

Unfortunately, I can't use data entry type, as the query is in place to show me equipment due for testing. So the form shows those records, with a check box which confirms receipt of the equipment for testing.

I'm pretty sure I can do what I am looking for by amending the event code as quoted, but I just don't know how to build it.

Thanks.
Ged.

 

[CBrighton]

If the form is unbound, then yes you can have VBA behind the after update event creating a new record:

dim db as database
dim rst as recordset
 
set db = currentdb
set rst = db.openrecordset("SELECT * FROM tblTableName")
rst.addnew
If Check96 = True Then
rst![Status] = "Returned (Pending)"
rst![Date received] = Date
rst.update
End If

Note that you will need to include any other fields (such as the field linked to the other table, and anything else which does not have a default value in the table).

If the form is bound this new record will not inherit the values of any other controls on the field unless you explicitly tell it to in the VBA.

 

[Gerdagan]

If the form is unbound, then yes you can have VBA behind the after update event creating a new record:

dim db as database
dim rst as recordset
 
set db = currentdb
set rst = db.openrecordset("SELECT * FROM tblTableName")
rst.addnew
If Check96 = True Then
rst![Status] = "Returned (Pending)"
rst![Date received] = Date
rst.update
End If

Note that you will need to include any other fields (such as the field linked to the other table, and anything else which does not have a default value in the table).

If the form is bound this new record will not inherit the values of any other controls on the field unless you explicitly tell it to in the VBA.

Well I've set-it up as you said, but its still not working.

When you say is the form bound, do you mean it has a source? If so, then it is bound to the Due for testing query.

The two tables, Equipment and Test History are linked with a unique ID. Do I need to include this in the coding? Is this what you meant by "field linked to the other tablefield linked to the other table?" How would I do this?

Sorry, I am learning as I go here. Everything else is working for me apart from this one little thing.

 

[CBrighton]

When using a virtual recordset in VBA, the record you are opening has no relation to what you see on your screen unless you specifically tell the code to take it into account.

The code posted above would make a new record with only the fields you populated in the VBA. You will need to add the other required fields in the same sort of way.

However, using fields on a bound form to add a new record is an odd way of doing things, it would be far simpler to have a command button which moves the user to a new record (and clears all controls ready fior the user to input the new data). That way there is no need for a virtual recordset as the bound form does all the work.

 

[Gerdagan]

Thanks for that.

But I've been taking a look into Append queries further (as suggested by GinaWhipp), and this seems to be the way I will be able to do what I want to do.

Thanks for all your input guys. I really appreciate it.

 

[CBrighton]

The append query will do the same as the VBA recordset.

As long as you correctly set it to pull whatever other pieces of data (FK's, etc) from an existing record it will be fine.

However if the append query only has the 2 fields which we used in the VBA specified then you will once again have a new record with only those 2 fields populated.

 

[GinaWhipp]

I got tied up today but I see you are going to try an APPEND query. We'll be standing by if that doesn't give you what you need...

 

[Gerdagan]

I got tied up today but I see you are going to try an APPEND query. We'll be standing by if that doesn't give you what you need...

Thanks very much, the append query worked. I had to change a few things to make it work but it is much easier than anything else I could have tried. Thanks.

 

[GinaWhipp]

Glad to help!