Write to table on form open. (2000)

Topic

New Reply

Is there a way to automatically write certain records in a table when a form is open using VB?

Reply | Quote

Replies

This may be bad advice, but create a macro to run a query to write to the table, then on the on open property of your form; run the that macro. Then convert you macros to vb.

Reply | Quote

This may be bad advice, but create a macro to run a query to write to the table, then on the on open property of your form; run the that macro. Then convert you macros to vb.

Reply | Quote

Would you expand on what you want to do.

Reply | Quote

I have a subform that is set to continuous. When the form is brought up for the very first time, the subform is empty. When the user inputs data, it is stored to my table. Then the next time the form is opened, a query is run and certain records are displayed. What I want to do is have 7 records already in the subform when it opens the first time. I can’t put them in the subform design becasue everytime I want a new record, it adds those 7 records. I only need these seven to be created the first time.

Reply | Quote

That doesn’t really explain enough. Do you want seven *empty* records created? That suggests a very poor table design. If you are populating some value in each record, that can be done in code, but it still leaves questions unanswered.

Reply | Quote

On the subform, the user gives the input – job#, ActivityCode, then inputs time in one of 7 textboxes (one for each day of the week) Some boxes will be null or every box can contain info. When this form is opend for the first time, I have 7 job numbers that I want to automatically show up. These are default that every user will use. The others will vary depending on which project they work on each week. So is there a way to create these seven records when the form is opened for the first time, or is there a better way of doing this?

Reply | Quote

On the subform, the user gives the input – job#, ActivityCode, then inputs time in one of 7 textboxes (one for each day of the week) Some boxes will be null or every box can contain info. When this form is opend for the first time, I have 7 job numbers that I want to automatically show up. These are default that every user will use. The others will vary depending on which project they work on each week. So is there a way to create these seven records when the form is opened for the first time, or is there a better way of doing this?

Reply | Quote

That doesn’t really explain enough. Do you want seven *empty* records created? That suggests a very poor table design. If you are populating some value in each record, that can be done in code, but it still leaves questions unanswered.

Reply | Quote

Why don’t you do it manually, if it is only to be done the very first time the main form is opened.

If you want to populate the subform records everytime the main form is opened then put the code in the OnOpen event of the main form.

I still don’t get what you are trying to do or why.

Reply | Quote

I really don’t understand why it has to be done this way either. The request asked for it to be this way and I am trying to figure it out.

Reply | Quote

You have to question requests that you don’t understand, especially if they come from users, who haven’t a clue as to HOW any of this should be done. Find out what the requester is trying to accomplish. Then come back with your questions about how to do it and *whether* to do it that particular way.

Reply | Quote

Post deleted by threeieng11774

Reply | Quote

That’s what they want to accomplish, not why they want to do it that way. It’s easy to get the distinction confused because they sound like the same thing, but they really aren’t. Keep in mind that they are often simpler ways to do things. For instance, you could provide 7 buttons with the name of a day on each button and pop up an input box to accept the information for that day when they clicked on it. Then your form could create a record for that day.

Reply | Quote

That’s what they want to accomplish, not why they want to do it that way. It’s easy to get the distinction confused because they sound like the same thing, but they really aren’t. Keep in mind that they are often simpler ways to do things. For instance, you could provide 7 buttons with the name of a day on each button and pop up an input box to accept the information for that day when they clicked on it. Then your form could create a record for that day.

Reply | Quote

Is the problem that when you write a new record to the table behind the main form, then it needs to write the 7 records to the table behind the subform?

If so then put the code in the AfterInsert on the main form.

Reply | Quote

Can you please give a sample of what the code should be?

Reply | Quote

Does this response mean you agree that when a record is written in the main form then the 7 records need to be written in the subform?

You will have to give me more details. Better than that why don’t you send a compacted/zipped version of your database giving us the details of what form you are doing this in.

Reply | Quote

Additional information is in post 426105, which is now locked to keep the topic together.

Reply | Quote

Can anyone please post an example that uses the AddNew method? I am trying to store 7 records to the table when a button is pressed.

Reply | Quote

Dim dbs as DAO.Database, rs as DAO.Recordset
Set dbs = CurrentDB
Set rs = dbs.OpenRecordset(“your tablename”)
rs.AddNew
rs!yourfieldnam1 = whatever
rs!yourfieldname2 = “text”
..
..
rs.Update
Set rs = Nothing
Set dbs = Nothing

Reply | Quote

Here is my code:

Private Sub btnAddRecord_Click()

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(“tblTime”)
rs.AddNew
rs!fname = “Jane”
rs!lname = “Brown”
rs.Update
Set rs = Nothing
Set dbs = Nothing

End Sub

The compiler highlights dbs as DAO.Database and says “User-defined type not defined”. Any suggestions?

Reply | Quote

Here is my code:

Private Sub btnAddRecord_Click()

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(“tblTime”)
rs.AddNew
rs!fname = “Jane”
rs!lname = “Brown”
rs.Update
Set rs = Nothing
Set dbs = Nothing

End Sub

The compiler highlights dbs as DAO.Database and says “User-defined type not defined”. Any suggestions?

Reply | Quote

Dim dbs as DAO.Database, rs as DAO.Recordset
Set dbs = CurrentDB
Set rs = dbs.OpenRecordset(“your tablename”)
rs.AddNew
rs!yourfieldnam1 = whatever
rs!yourfieldname2 = “text”
..
..
rs.Update
Set rs = Nothing
Set dbs = Nothing

Reply | Quote

Building on Pat’s example, which will add one record, try this …

Dim dbs as DAO.Database
Dim rs as DAO.Recordset
Dim intLoop As Integer

Set dbs = CurrentDB
Set rs = dbs.OpenRecordset("your tablename")
For intLoop = 1 to 7
rs.AddNew
rs!yourfieldnam1 = whatever
rs!yourfieldname2 = "text"
..
..
rs.Update
Next intLoop
Set rs = Nothing
Set dbs = Nothing

Reply | Quote

Building on Pat’s example, which will add one record, try this …

Dim dbs as DAO.Database
Dim rs as DAO.Recordset
Dim intLoop As Integer

Set dbs = CurrentDB
Set rs = dbs.OpenRecordset("your tablename")
For intLoop = 1 to 7
rs.AddNew
rs!yourfieldnam1 = whatever
rs!yourfieldname2 = "text"
..
..
rs.Update
Next intLoop
Set rs = Nothing
Set dbs = Nothing

Reply | Quote

Pat’s and Charlotte’s code are DAO code. You’ll have to set a reference to it.
In the VBE editor, select Tools / References and scroll down to Microsoft DAO 3.6 Object Library. Check the check box and close the reference window.

Reply | Quote

Pat’s and Charlotte’s code are DAO code. You’ll have to set a reference to it.
In the VBE editor, select Tools / References and scroll down to Microsoft DAO 3.6 Object Library. Check the check box and close the reference window.

Reply | Quote

Can anyone please post an example that uses the AddNew method? I am trying to store 7 records to the table when a button is pressed.

Reply | Quote

Additional information is in post 426105, which is now locked to keep the topic together.

Reply | Quote

Does this response mean you agree that when a record is written in the main form then the 7 records need to be written in the subform?

You will have to give me more details. Better than that why don’t you send a compacted/zipped version of your database giving us the details of what form you are doing this in.

Reply | Quote

Can you please give a sample of what the code should be?

Reply | Quote

Is the problem that when you write a new record to the table behind the main form, then it needs to write the 7 records to the table behind the subform?

If so then put the code in the AfterInsert on the main form.

Reply | Quote

Post deleted by threeieng11774

Reply | Quote

You have to question requests that you don’t understand, especially if they come from users, who haven’t a clue as to HOW any of this should be done. Find out what the requester is trying to accomplish. Then come back with your questions about how to do it and *whether* to do it that particular way.

Reply | Quote

I really don’t understand why it has to be done this way either. The request asked for it to be this way and I am trying to figure it out.

Reply | Quote

Why don’t you do it manually, if it is only to be done the very first time the main form is opened.

If you want to populate the subform records everytime the main form is opened then put the code in the OnOpen event of the main form.

I still don’t get what you are trying to do or why.

Reply | Quote

I have a subform that is set to continuous. When the form is brought up for the very first time, the subform is empty. When the user inputs data, it is stored to my table. Then the next time the form is opened, a query is run and certain records are displayed. What I want to do is have 7 records already in the subform when it opens the first time. I can’t put them in the subform design becasue everytime I want a new record, it adds those 7 records. I only need these seven to be created the first time.

Reply | Quote

Would you expand on what you want to do.

Reply | Quote