Problem coding forms to feed tables (1 Viewer)

idforgod · Dec 20, 2018

Working on my first database in access but encounter problem feeding data to table using forms.
I have tried a couple of methods but it not giving me what I want, any idea on how to go about it.
Looking forward to hearing from you guys. Thanks

arnelgp · Dec 20, 2018

give more detail.
is the form bound to a table?

Uncle Gizmo · Dec 20, 2018

idforgod said:
encounter problem feeding data to table using forms.
I have tried a couple of methods

What have you tried?

Have you tried this:-

Select the table, then select the form design wizard and it will create a form for entering data in to the selected table....

Sent from my SM-G925F using Tapatalk

idforgod · Dec 20, 2018

Yes I know that method, but I want to use button to insert the data,
My problem is programming the botton,
I tried using INSERT INTO function but it not working

arnelgp · Dec 20, 2018

unbound form is bad.
but if you prefer it you can use the Click event of the button to insert:

Code:

Private Sub button_Click()
With CurrentDb.CreateQueryDef("", "Insert Into table (field1, field2) select @1, 
   @2;")
   .parameters(0)=me.textbox1.value
   .parameters(1)=me.textbox2.value
   .execute
End With
End Sub

Cliff67 · Dec 20, 2018

can you copy your code so we can look at it?

idforgod · Dec 20, 2018

Here is the code I used.

Private Sub cmdSave_Click()
'add data to table
CurrentDb.Execute "INSERT INTO childDetails(SurName, OtherNames, DateOfBirth, PlaceOfBirth, Gender, StateOfOrigin, LGA, Attachment, PreviousSchool) " & _
" values(" & Me.txtSurName & ",'" & Me.txtOtherName & "','" & _
Me.txtDateOfBirht & "', '" & Me.txtDateOfBirht & "', '" & Me.cmbGender & "','" & Me.txtStateOforigin & "','" & Me.txtLGA & "','" & Me.txtPreviousSchool & "')"

If I run the code it does not execute nither does it show any error.
Is there anything am missing here?

Thanks

Cliff67 · Dec 20, 2018

Have you spelt the text fields correctly e.g txtDateofBirht not txtDateofBirth?

it also seems like you have too many quotes in there somewhere

try to copy and paste into a query in SQL and see if it works that way. The QBE will give you the correct way to do it (minus the Currentdb.Execute bit)

Cliff67 · Dec 20, 2018

Have you done any error trapping or coding around the code? Do you have any way to show the errors like a message box?

Cliff67 · Dec 20, 2018

You don't have matching destination fields, you are missing the "attachment". You are also missing single quotes around & Me.txtSurName &

Cliff67 · Dec 20, 2018

Your code should look like

Code:

CurrentDB.Execute "INSERT INTO childDetails(SurName, OtherNames, DateOfBirth, PlaceOfBirth, Gender, StateOfOrigin, LGA, Attachment, PreviousSchool) values(' & Me.txtSurName &' ,' & Me.txtOtherName & ','Me.txtDateOfBirht & ', ' & Me.txtDateOfBirht & ', ' & Me.cmbGender & ',' & Me.txtStateOforigin & ',' & Me.txtLGA & ',' & Me.txtAttachment &', '& Me.txtPreviousSchool & ')"

James Dickinson · Dec 20, 2018

incorrect line continuation and missing single quotes.

Code:

CurrentDB.Execute "INSERT INTO childDetails(SurName, OtherNames, DateOfBirth, PlaceOfBirth, Gender, StateOfOrigin, LGA, Attachment, PreviousSchool)" _
& " values('" & Me.txtSurName & "' ,'" & Me.txtOtherName & "','" & Me.txtDateOfBirht & "', '" & Me.txtDateOfBirht & "', '" & Me.cmbGender & "','" & Me.txtStateOforigin & "','" & Me.txtLGA & "','" & Me.txtAttachment & "', '" & Me.txtPreviousSchool & "')"

Cliff67 · Dec 21, 2018

Thanks for the correction James

idforgod · Dec 21, 2018

Thanks allot @cliff67 and @James Dickinson. Your code seems to have worked only that am have Compile error while running the code the heading of the error is "Method or data member not found" while highlighting me.LGA in the code.

I have checked my forms as well as the reference table to make sure that those names of my text box and fields are accurate or correspond with those in the code.
Is there anything I should do?
I really appreciate you efforts in resolving my problem.

Cliff67 · Dec 21, 2018

is it the me.txtGLA bit or the GLA of the childDetials table that it is highlighting?

idforgod · Dec 21, 2018

It highlighted Me.txtLGA @cliff67 before it was Me.Surname as I tried debugging it it now jump to Me.LGA.

I noticed that the values appears in the table childDeail but anytime I changed it override the previous one instead of inserting.

idforgod · Dec 21, 2018

[CurrentDB.Execute "INSERT INTO childDetails(SurName, OtherNames, DateOfBirth, PlaceOfBirth, Gender, StateOfOrigin, LGA, Attachment, PreviousSchool)" _
& " values('" & Me.txtSurName & "' ,'" & Me.txtOtherName & "','" & Me.txtDateOfBirht & "', '" & Me.txtDateOfBirht & "', '" & Me.cmbGender & "','" & Me.txtStateOforigin & "','" & Me.txtLGA & "','" & Me.txtAttachment & "', '" & Me.txtPreviousSchool & "')"]

Cliff67 · Dec 21, 2018

You might be missing a reference to Microsoft Access 15.0 object Library (or what ever version you are using).
In the VBA code window select Tools from the menu then select References and check you have the reference to the above library, then re-compile your database.

At the beginning of your form you have added Option Explicit under the Option Compare Database?

Cliff67 · Dec 21, 2018

Have you tried to do this in an Append query to see if it works correctly?

isladogs · Dec 21, 2018

Four things:
1. Remove the [] brackets at the start and end
2. You are using the value from the same textbox Me.txtDateOfBirht twice.
The second should presumably be Me.txtPlaceOfBirth
3. Check the spelling Me.txtDateOfBirth???
4. As DateOfBirth is a date field, you need date delimiters

Code:

CurrentDB.Execute "INSERT INTO childDetails(SurName, OtherNames, DateOfBirth, PlaceOfBirth, Gender, StateOfOrigin, LGA, Attachment, PreviousSchool)" _
& " values('" & Me.txtSurName & "' ,'" & Me.txtOtherName & "', [COLOR="red"]#" & Me.txtDateOfBirth & "#, '" & Me.txtPlaceOfBirth & "[/COLOR]', '" & Me.cmbGender & "','" & Me.txtStateOforigin & "','" & Me.txtLGA & "','" & Me.txtAttachment & "', '" & Me.txtPreviousSchool & "')"

Problem coding forms to feed tables (1 Viewer)

Registered User.

..forever waiting... waiting for jellybean!

Nifty Access Guy

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

PigeonPie

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

MVP / VIP

Similar threads

Users who are viewing this thread