I try to write records from a field of a query to an irrelevant table..

[DKpitbull]

Hi all!

I have a query with many fields. One of them is the combination of two fields and is called "Components Reference". What I try to do is, every time this query is updated, the records of only this field "Components Reference" to be written to the field of another table which also takes records from an another query. I wish I was clear enough..

Any ideas???

Thanks!!

 

[pr2-eugin]

I wish I was clear enough

I wish the same ! could you give some example and explain what you intend to do? Also where you intend to do this sort of operation.

 

[DKpitbull]

OK...!! Initially, thanks for your reply.

I have a form which is based on a query. In this form I have text boxes where I can modify/add records and save them to the relevant table of this form (the table from which my query was created). Now what I want is, the content of a specific text in the form to be able to save it in the field of another irrelevant table.
In other words, to save a record in two different irrelevant tables.

Table name: "tblTransistor"
Query name: "queryTransistor"
Form name: "Transistor Catalogue"

Contains many text boxes like, "Table Type", "Part Ref"....etc

I want to be able to save the "Part Ref" in the specifific field of another table (tblManComps) as well, which is called "Components Ref".

So, the table "tblManComps" has the field with name "Components Ref".

Thus, when the user creates a new record in the form "Transistor Catalogue", the information of the "Part Ref" to be saved in the table "tblTransistor" and in the table "tblManComps".

So simple...lol :banghead:

I think I was clear enough this time

 

[pr2-eugin]

I think I was clear enough this time

Much better. So you use a UPDATE statement, I hope you have a button on the form which will close the form, so you can have something like.

Private Sub buttonName_Click()
    If Me.NewRecord Then _
        CurrentDB.Execute "INSERT INTO tblManComps ([Components Ref]) VALUES ('" & Me.[Part Ref] & "')"
End Sub

I am guessing Component Ref is a String. Also if it is not too late, I would highly recommend you to follow a good MS Access Naming Convention

 

[DKpitbull]

It seems not to work. The strange is that no error message is appeared, but the record is not saved to the ManComps table. I typed the expression in the AfterUpdate event of the form. Is that ok? The form is updated its time you go to the next/new record or when I close it.

 

[pr2-eugin]

I typed the expression in the AfterUpdate event of the form. Is that ok?

No ! Create a button to close the form down, this button will also do this update to the table instead of using AfterUpdate event.

The form is updated its time you go to the next/new record or when I close it.

AfterUpdate is not the best method, I would not suggest using this. Use button.

 

[DKpitbull]

I typed the expression in the On_Click event of the button but again nothing. It saves the record only to the one of the two tables (the one that was created from).

In your "If..Then" expression you have an underscore after "Then". Is that correct?
I don't know what can be wrong..

 

[pr2-eugin]

This will add the record in the table only of it is a New record ! That is what the If states.

 

[DKpitbull]

Yep, I understand the statement, but it doesn't work.

 

[pr2-eugin]

Can you please upload a Stripped DB?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[DKpitbull]

pr2-eugin,

I have cleaned/ chopped almost everything.

In the form you have: Index(type a number), Table type(you don't have to type anything), Part Ref (type anything) and the Component(it is filled automatically, as it is the combination of Index+Table type).

What I want is to be able to save the "Component" automatically to the "Catalogue Ref" field of "ManComps" table.

*Previously I gave you the Part Ref as the idea is the same and I didn't want to confuse you as it is the combination of two fields of "Transistor Catalogue" table.

Thanks again pr2-eugin!

 

[DKpitbull]

I use Access 2010..

 

[DKpitbull]

Any ideas guys? Urgent!!

 

[DKpitbull]

Answer:

Add the [Component] field to the table.
In the Before_UpDate event of the form type:

If (Me.NewRecord) Then _
[Component] = [TableType] & Format([Index], "00000")
CurrentDb.Execute "INSERT INTO ManComps ([Catalogue Ref]) VALUES ('" & Me.[Component] & "')"

Problem solved!