Form with text box for adding Notes using linked table

[Catalyst159]

I have a form called "Problem Records Details" which contains a Text Box called "txtNotes".

The Record Source for the "Problem Records Details" form is a linked table called "dbo_proble ms". However all Notes are stored in a linked table called "dbo_tblNot es". The "ID" field in the "dbo_tblNot es" table references the "ProblemID" field in the "dbo_proble ms" table. I want the the user to be able to enter notes in the using the "txtNotes" text box on "Problem Records Details" form. If notes are entered in the form and save is clicked then any notes entered need to be inserted into the "dbo_tblNot es" table with the correct "ID" referencing the same "ProblemID" of the record displayed in the "Problem Records Details" form.

Any help, insight, or suggestions are greatly appreciated. Thank you in advance.

Regards,

Catalyst

PS. Would something like the following work:

currentdb.execute ("INSERT INTO dbo_tblNotes (id, note, addDate, uid, private) VALUES ('" & me.ID & "','" & me.txtNotes & "','" & Now() & "','" & me.Username & "','" & me.txtpriority & "') ;"

[Rabbit]

Why do it through code? Why not use a subform?

[NeoPa]

It's fundamentally flawed as the SQL will (should) not work (unless your design is worse than bad).

That said, it's not even a good way to think about doing what you need. You should be thinking of using a Subform on your main form which links to the parent and allows normal form updates of the data. A much more straightforward approach that is already inbuilt into Access.

[Catalyst159]

How should the SQL look?

[NeoPa]

The values in the VALUE () set should be surrounded by characters appropriate to the type of the field. I cannot say definitively how each should be as I have no information concerning your data. Currently though, all values appear to be strings. You should look at Quotes (') and Double-Quotes (") - Where and When to use them and Literal DateTimes and Their Delimiters (#) for a fuller understanding.

NB. Although getting the SQL right may be interesting in its own right, it's not a substitute for handling this problem correctly, which is to use the Subform feature MS built in to the Access product.

[Catalyst159]

Sounds like the right path for me. Thank you for the resourceful links. I will check them out, have a better understanding and then post my thought on it. Thanks NeoPa

Regards,

Catalyst

[Catalyst159]

How would you use a subform?

[Rabbit]

I would put a subform on the problems form. That subform's record source would be the notes table. The main form would be linked to the subform on some problem id.

[Catalyst159]

OK..I will give it a try.

[Catalyst159]

I put a subform on the Problem Details Form. The subform is named "Child43".

When I try to set the source object for "Child43" dbo_tblNotes is not listed as one of the choices.

Why would that be?

[Catalyst159]

I want to have a text box to add notes. This would only be for input. I do not want to display notes in this text box. Only use it for entering notes.

[NeoPa]

Catalyst159:
When I try to set the source object for "Child43" dbo_tblNotes is not listed as one of the choices.

Are you sure? Did you look for Table.dbo_tblNo tes?

If so, then is it even listed in your Tables list on the database front screen?

[NeoPa]

Catalyst159:
I want to have a text box to add notes. This would only be for input. I do not want to display notes in this text box. Only use it for entering notes.

This is a separate question, but look at the properties of the form you intend to use and set Data Entry to True.

[Catalyst159]

Yes the table is listed on the database front screen.

I have set the Data Entry property to True, but no change.

[NeoPa]

Catalyst159:
Yes the table is listed on the database front screen.

That doesn't answer all the questions.

Catalyst159:
I have set the Data Entry property to True, but no change.

As I said, this is a separate question. If you want to go into it in any detail then you'll need to post it as a question.