Enter data in subform with button (1 Viewer)

[Mantispony]

Hi all,

I'm not even sure whether or not this is possible, but this is my challenge:

I've got a list of customers and their sales value etc. Now my boss wants to be able to enter comments (for some reason the memo field columnhistory thing does not work for me, boo) that are dated automatically.

So I figured I'd use a subform, the customer ID being the field that links them, so the comments will show up in the subform.

I want a button to automatically enter the customer ID number from the motherform into the childform's corresponding column so the comments will stay linked to the customer.

I'm a VBA novice, so if this requires VBA (or any other programming), please pretend you're explaining it to a 3-year-old.

Thank you!!

 

[vapid2323]

I will give this a shot, there might be a better way but this is how i see it

I would use VBA for the On_Click of a command button. It will need som code but I think you should be able to get it.

You will need to get the table name your subform is working with and the one or two fields you need to add.

DoCmd.RunSQL "INSERT INTO tblDocsMgr ([fk_SiteID]) VALUES (" & Me.fk_SiteID.Value & ");"

the above code, when run, will Insert the whatever value is found in my fk_siteID text box into the table. This would add a new record into the subform.

After you have done this you would need to run Me.Requery to update the subforms data, that way we can see this new record added by the code above. All the Requery is doing is looking for any changes made to the subforms table.

Now you need to update the code above to fit your needs, you need to change the Table, Field that we are adding the record too and lastly where we are getting the data from.

The code below will add in three records into a table, it should help you understand a little more about the code you need to create.

DoCmd.RunSQL "INSERT INTO [B]tblObservation[/B] ([[B]fk_QualificationID[/B]], [[B]fk_SiteID[/B]], [[B]IsChairRecord[/B]]) VALUES (" & [B]Me.QualificationID.Value[/B] & "," & [B]Me.fk_SiteID.Value[/B] & "," & [B]-1[/B] & ");"

 

[JANR]

If your form and subform is bound to your table, then just use the LinkMaster/LinkChild field property of your subformconteiner and let access fill in the link.

LinkMaster Field -> CustomerID
LinkChild Field -> CustomerFK


JR

 

[Mantispony]

JANR, I think it would make more sense to use a separate table..
Vapid, ..I can't follow you, sorry, I don't know enough about coding/programming.. Can you please explain what a me.query is and how to make one?

 

[JANR]

JANR, I think it would make more sense to use a separate table

If your form and subform is BOUND to your table then why not let Access to the work for you.

Are the forms Bound?

JR

 

[Galaxiom]

You should be glad that the Memo didn't work for you. JANR's suggestion of a related table bound to a subfom is is far superior to updating a memo field and far more relaible.

Indeed I would recommend disabling edits of the existing records and only allow new records. This prevents anyone removing previous comments. Edits and deletions can be allowed for certain authorised users.

Vapid's solution is unnecessarily complex compared to a bound form.

 

[Mantispony]

Ok, thanks for your feedback and opinions!

So I'll go with JANR's solution then, but I'm not entirely sure I understand the mechanics..

If I bind both the form and the subform to the same table, and the subform adds records, won't I get loads of records with the same ClientID number? Or will it just add columns to my main table, or am I missing the whole point again?

 

[JANR]

If I bind both the form and the subform to the same table

No, no. A subform cannot use the same recordsource as the mainform, you will lock yourself out when you try to enter something in the subform. My impression was that you had the memofield in a seperate table linked to tblCustomer throu customerID.

tblCustomer:
CustomerID
CustomerName
...Other relevant fields

tblCustomerNotes
CustomerFK <--- ForeignKey to tblCustomer
CustomerNotes <- MemoField

Now you based the mainform on tblCustomer and add a subform based on tblCustomerNotes and in the subform hide the CustomerID field and only show CustomerNotes.

To get Access to insert the CustomerID into tblCustomerNotes you click on the subformcontrol and open the propetysheet and under the Datatab you find LinkMaster field and LinkChild Field. Simply type in CustomerID in LinkMaster property and CustomerFK in the LinkChild property.

If you already have the relationship defined then access will do this job for you, simply drag the table tblCustomerNotes onto the form.

Hope this helps

JR

 

[Mantispony]

Ahhhh ok now it makes sense to me, thanks!

..but what is a ForeignKey and what does it do? (I'm just curious now)

 

[JANR]

A foreignkey is the same ID as the CustomerID in the Customer table, so if CustomerID = 125 then if that customer has a note then CustomerFK will also have an ID of 125. It is adviseble to use numbers as keys because Acccess works faster and better with numbers than text.

So if you want to query your database on which customers has a note attaced to it, then simply create a query and select both tables and join CustomerID and CustomerFK and when you run the query it will show only customers with their corresponding notes where those numbers are equal.

JR

 

[Mantispony]

..amazing! Sorry, I can really be amazed by what technology can do! Thanks a million!!

 

[Galaxiom]

Depends how you set it up. If you want history then prevent Deletions and Edits and only add new records.

The subform would be displayed in Datasheet or Continuous Forms so that all the entries are visible at once.

 

[Mantispony]

Depends how you set it up. If you want history then prevent Deletions and Edits and only add new records.

The subform would be displayed in Datasheet or Continuous Forms so that all the entries are visible at once.

How do I set it to "only add new records"?

 

[Galaxiom]

In the Properties of the Form that is displayed inside the subformcontrol.

Set AllowEdits and AllowDeletions to No