Tabbed Data Entry Form

[papadega3000]

Hello,

I am trying to make a form that will enter data in 3 different tables. I created a tabbed form with 3 pages. Each page corresponding to a separate table.
In Design view I set the Form Record Source to include the three tables I want to enter data to and set it to data entry = yes.

I added a submit button that would execute the following VBA code:

Private sub Submit_Click()

'inserts into Table1
CurrentDb.Execute "INSERT INTO Table1(field1,field2,field3,field4,field5,field6) VALUES ('" & field1.Value & "','" & field2.Value & "','" & field3.Value & "','" & field4.Value & "','" & field5.Value & "','" & field6.Value & "')"

' inserts into Table2
CurrentDb.Execute "INSERT INTO Table2(field1,field2,field3,field4,field5,field6) VALUES ('" & field1.Value & "','" & field2.Value & "','" & field3.Value & "','" & field4.Value & "','" & field5.Value & "','" & field6.Value & "')"
inserts into Table3
CurrentDb.Execute "INSERT INTO Table3(field1,field2,field3,field4,field5,field6) VALUES ('" & field1.Value & "','" & field2.Value & "','" & field3.Value & "','" & field4.Value & "','" & field5.Value & "','" & field6.Value & "')"

End Sub

Now when I execute the code two issues occur:

1. I can enter text into the first page but the other pages don't let me enter text in the boxes.
2. I get a run-time error on each line of code saying " Run-time error 424 - Object Required"

However the data entered into the first page shows up in the table after I re-open it.

I would appreciate any help I can get I would like to solve this problem as soon as possible.

 

[JonyBravo]

Hi

I would do like this:

Private sub Submit_Click()
dim sql

'inserts into Table1
sql = "INSERT INTO Table1(field1,field2,field3,field4,field5,field6) VALUES ('" & field1.Value & "','" & field2.Value & "','" & field3.Value & "','" & field4.Value & "','" & field5.Value & "','" & field6.Value & "')"
docmd.runsql sql

' inserts into Table2
sql = "INSERT INTO Table2(field1,field2,field3,field4,field5,field6) VALUES ('" & field1.Value & "','" & field2.Value & "','" & field3.Value & "','" & field4.Value & "','" & field5.Value & "','" & field6.Value & "')"
docmd.runsql sql

inserts into Table3
sql = "INSERT INTO Table3(field1,field2,field3,field4,field5,field6) VALUES ('" & field1.Value & "','" & field2.Value & "','" & field3.Value & "','" & field4.Value & "','" & field5.Value & "','" & field6.Value & "')"
docmd.runsql sql

End Sub

 

[papadega3000]

Thank you for your response.
I am still receiving Object required run-time errors when executed.

However, when it got the statement that controlled the pages I could not enter data in it threw out an error saying it cannot append a row on NULL data.... which makes sense because nothing is in the form since i cannot edit that text box.

Any paticular reason why it still gives me an object required on INSERT statement?

And if there is something special I need to do to make the other pages have the ability to enter data that I am missing let me know.

Any information will help.

 

[Dreamweaver]

Can I ask why you need to add the same data to 3 tables????

 

[papadega3000]

I am sorry I probably wasn't very clear in my explanation.

I have 3 tables with different data. I wanted to make one form that would allow me to enter the data for all three tables then hit a submit button to insert it into the respective tables.

The idea was to make a data entry form that consisted of 3 pages and each page would be insert data into the table I specify. This would make user input easier than having a separate form for each table.

The problems I am having is making the data entry form enter the data into the 3 different tables using a command button.

The code posted in previous posts was the code I am using to try and accomplish this.

I am not sure if what I want to do is possible.

 

[Dreamweaver]

It's possible all right but the sql you have posted for the 3 tables relates to the same controls but you need to remember even though you have tabs each page will have different control names so from the looks of that your trying to add whats on tab page 0 only.

Look at the rowsource of each text box on pages 2 and 3 make sure they relate to the correct table.

also do you have the required property set to yes for any of the table fields in 2 and 3.

gotta pop out now back later.

 

[papadega3000]

I was unable to find row source but I am assuming you meant control source.

Looking at my properties window for each text box on all the pages including page 0. has a control source selected as the name of the field from the correct table. The name field in the properties window is the same as the control source also.

I also checked the properties of the entire Form. The control source there is
is set to a long string consisting of all the fields names throughout all three tables.I am not sure how to set the property so I am going to say I don' t have that set to Yes.

In my code do I have to specify the Page like this:

dim sql

'inserts into Table1
sql = "INSERT INTO Table1(field1,field2,field3,field4,field5,field6) VALUES ('" & Page0.field1.Value & "','" & Page0.field2.Value & "','" & Page0.field3.Value & "','" & Page0.field4.Value & "','" & Page0.field5.Value & "','" & Page0.field6.Value & "')"
docmd.runsql sql

'inserts into Table2
sql = "INSERT INTO Table2(fielda,fieldb,fieldc,fieldd,fielde,fieldf) VALUES ('" & Page1.fielda.Value & "','" & Page1.fieldb.Value & "','" & Page1.fieldc.Value & "','" & Page1.fieldd.Value & "','" & Page1.fielde.Value & "','" & Page1.fieldf.Value & "')"
docmd.runsql sql

'inserts into Table3
sql = "INSERT INTO Table3(fieldA,fieldB,fieldC,fieldD,fieldE,fieldF) VALUES ('" & Page2.fieldA.Value & "','" & Page2.fieldB.Value & "','" & Page2.fieldC.Value & "','" & Page2.fieldD.Value & "','" & Page2.fieldE.Value & "','" & Page2.fieldF.Value & "')"
docmd.runsql sql

Thanks for your help!

 

[Dreamweaver]

All pages of tabbed pages are in actual fact one big page and as god said there can only be one control with the same name on a form IE in this case all tabbed pages then on page one assuming you haven't used subforms.

So Page one controls could be
field1
field2
field3
field4
Page 2
field5
field6
field7
field8
Page 3
field9
field10
field11
field12
And So On

If you have just copied the first pages controls then ACCESS will assign new names to those that are already in use.

Using Page.control.value wont work or at least don't think so I only ever use Me![ControlName]

Maybe you should post what you have done.

mick

 

[papadega3000]

Thanks for your response again... I appreciate your help I have been researching this area for I am not that familiar with writing code for forms. I have taken your advice and checked all of the names and controls. The way I have it is that each control and name are the same only for one text box.
I didn't edit anything...
The way I created the form was in Design View--> went to Properties--> Set Data Entry =Yes--> Went to Record Source--> clicked the ...--> Then selected the Tables I wanted to be in the form. --> Went back to the form--> Made the Tabbed Page-->Clicked and dragged over all of the fields I wanted for each page.

So Access had created the names for me.

No matter what I try it still only allows me to enter text in Page0. I am going to try what you suggested in your last post. See where it gets me.

I am afraid I cannot distrubute my work for work-related reasons.
Although I appreciate your advice it has opened some doors for me.

Thanks again

 

[papadega3000]

Ok... I am still having problems getting data entered into the table. I have simplified my form down to just one table to see if I can get my code to work...unfortunately this doesn't work.

Here is what I have done with my code:

Original Code:

Private Sub cmdSubmit_Click()

CurrentDb.Execute "INSERT INTO Table1(field1,field2,field3,field4,field5,field6) VALUES ('" & field1.Value & "','" & field2.Value & "','" & field3.Value & "','" & field4.Value & "','" & field5.Value & "','" & field6.Value & "')"

CurrentDb.Execute "INSERT INTO Table1(Field7) VALUES (#" & Format(Field7.Value, "mm/dd/yyyy") & "#)"

End Sub

This gives me a runtime error saying object required. I double-checked my spelling and syntax everything seems up to par. In debug mode I can see the values getting assigned to each field. It is just not getting past that first line of code. Is there other parameters to CurrentDb.Execute that I am missing.


Then I tried the suggestion from above:

Private Sub cmdSubmit_Click()
CurrentDb.Execute "INSERT INTO Table1(field1,field2,field3,field4,field5,field6) VALUES ('" & Me![field1].Value & "','" & Me![field2].Value & "','" & Me![field3].Value & "','" & Me![field4].Value & "','" & Me![field5].Value & "','" & Me![field6].Value & "')"

CurrentDb.Execute "INSERT INTO Table1(Field7) VALUES (#" & Format(Me![Field7].Value, "mm/dd/yyyy") & "#)"
End Sub


I am running out of idea's I had the forms that I made generate all of the boxes through the wizard so the names are correct. The only thing i thought it could be was that I have a field that has white space in its name. (ie. Company Name) I cannot write it that way. It gets represented as Company_Name. Is that correct? or could the problem be there?

It is obvious that the code I have originally does work because it will add the data after I break the code and re-open that table. But I don't want to have to do that everytime. I just want to click Submit and the data goes in the table.

Thanks.

 

[papadega3000]

I resolved the issue in a re-design of the orientation of my forms. If I have more time and interest I will go back to having pages instead of separate forms.