Solved: Adding new records on another table

[Ryanr]

Hello there again :)

Basically, I am creating a small IT help desk database. I have a form where the user can enter in a problem and it stores this in the relevant table. However, I also need to add a new blank record to another table which is linked to this, is there an easy way of doing this?

I know the code for adding a new record using VBA on a table is 'DoCmd.GoToRecord , , acNewRec'

And for reference, the table I want to add a new record to is 'ITHELPPROBLEM'

I thought that the code would simply be 'Ithelpproblem.docmd.gotorecord ,, acnewrec'

I know I could simply add blank records myself, but being an IT apprentice, I want to learn as much as I can!

Please help :)

[stanl]

You will want to look into ADO to do what you want. I am confused when you say you want to add a blank record that is 'linked' to your primary table. Tables are linked by either primary or foreign 'keys'. So, let's suppose you main table is called tickets, and you have a field called Ticket_Number which holds a unique key (I prefer a field with yyyymmddhhmmss just to keep things organized). Anyway, then suppose your other table is ticket_solution - which might hold multiple rows describing the status of the ticket, who was working on it etc... then each of these rows needs a Ticket_Number field that holds the same key as the main table entry.

If you already knew this your post didn't seem to indicate that. There are a lot of freeware and sample mdb files related to ticket systems and you might spend a few minutes googling for them.

[Ryanr]

Sorry I didn't expalin myself very well haha :doh:

Basically, I am creating a simply IT helpdesk database. The process for reporting a problem is like this

- A person enters in their problem into a 'Report issue' form.
- The data they enter is then stored into a table that holds the problems called 'problems' via a button. This means that a new record is created in that table
- When this happens, I want to be able to create new records in two other tables at the same time. The 3 tables are linked together by an ID key and a relationship has been created between them all. The relationship has been created linking the ID keys together using a 'one-to-one' relationship

It is worth pointing out that the whole system works fine if I enter in blank fields by myself by going into the tables and entering in blank data. However, being new to Access, I want to find a method that isn't so clumbersome and reliant on the creator of the DB.

I'll look for examples though, I bet the solution is really simple and I missed something out haha :) Thank you!

[stanl]

Having developed over 12 ticket systems, you will be surprised how quickly the demands outrun the programming, even with the simplest of systems. I have always cut into those demands by making a a system with data-driven/user approved categories. I put this one into place last year in under 1 hour.... so good luck

[Imdabaum]

snip...
- A person enters in their problem into a 'Report issue' form.
- The data they enter is then stored into a table that holds the problems called 'problems' via a button. This means that a new record is created in that table
- When this happens, I want to be able to create new records in two other tables at the same time. The 3 tables are linked together by an ID key and a relationship has been created between them all. The relationship has been created linking the ID keys together using a 'one-to-one' relationship
Why one to one? I'm just thinking about the ticketing systems I've used and usually for one problem there are a many records related to it: ie;Comment, CommentDate

If I understand you have something like this
TblProblems
TBLOther1 and TBLOther2 'what is the purpose of these two tables.



It is worth pointing out that the whole system works fine if I enter in blank fields by myself by going into the tables and entering in blank data. However, being new to Access, I want to find a method that isn't so clumbersome and reliant on the creator of the DB.
As it should tables allow entry of data provided you enter values for all required fields..


I'll look for examples though, I bet the solution is really simple and I missed something out haha :) Thank you!

As part of the code though you might try this... but I'm an ameteur. :thumb


Private Sub cmdSUBMITPROBLEMBUTTON_CLICK() 'your button is clicked.
Dim db as Database
Dim rs as Recordset 'DAO or ADO
Set db = Currentdb
Set rs = db.OpenRecordset("TBLOTHER1", dbOpenDynaset)
With rs
.AddNew
.Fields("FieldName")
....
....
.Update
End With
rs.Close
db.Close
Set rs = nothing
Set db = nothing
End Sub



Not sure if that meets the needs of a non clumbersome method for the creator. Cumbersome is a rather relative term.:thumb
You could write some things for error trapping. If you want, close rs and set it to "TBLOTHER2" using copy and paste from this With rs and do the same thing with TBLOTHER2 afterwards. HTH.

[Ryanr]

Ah it turns out a simple Append Query was the answer to my problem, yes I didn't know about them which just shows how much I don't know haha..:whistle:

Thank you anyway guys, I just think I will need to learn more about the basics :)