error Transferring Data from form using VBA

[bh123]

Hi

I have successfully created the code below using an online tutorial. I have two forms whic are transferring to 2 different databases. The first form has worked no problem. I copied the code below to create the second transfer of data and added the number '1' to differentiate the two. It is not working. Please could you help me as where I have gone wrong. i have highlighted in Bold where i added 1. The reset seems to work ok.

thanks

Sub transfer()

Dim objList As ListObject ' to store or refer the table name CovidTestData
Dim shForm As Worksheet ' it will refer to the Worksheet 'Form"

Set objList = ThisWorkbook.Sheets("ResultDatabase").ListObjects("ResultData")
Set shForm = ThisWorkbook.Sheets("Result")

If objList.DataBodyRange(1, 1).Value <> "" Then


objList.ListRows.Add Position:=1



End If

With objList

.DataBodyRange(1, 1).Value = shForm.Range("G8").Value
.DataBodyRange(1, 2).Value = shForm.Range("G10").Value
.DataBodyRange(1, 3).Value = shForm.Range("G12").Value
.DataBodyRange(1, 4).Value = Application.UserName

End With

shForm.Range("G8,G10,G12").Value = ""

ThisWorkbook.Save



End Sub

Sub TransferToTable1()

Dim imessage As VbMsgBoxResult

imessage = MsgBox("Do you want to transfer this information to the Database?", vbYesNo + vbQuestion, "Confirmation")

If imessage = vbNo Then Exit Sub

Call transfer1



End Sub

Sub Reset1()

Dim imessage As VbMsgBoxResult

imessage = MsgBox("Do you want to reset the form?", vbYesNo + vbQuestion, "Confirmation")

If imessage = vbNo Then Exit Sub

Sheets("Result").Range("G8,G10").Value = ""
Sheets("Result").Range("G12").Value = "=now()"

End Sub

 

[Saurabhj]

Hi,

Where is transfer1 subprocedure? Have you defined that.

In the above you have shown only transfer subprocedure.

Thanks,
Saurabh

 

[bh123]

Hi,

Where is transfer1 subprocedure? Have you defined that.

In the above you have shown only transfer subprocedure.

Thanks,
Saurabh

Thank you for your reply Saurabh. The transfer 1 procedure is in Module1. This is in Module 2. Should I have added the above procedure to Module 1 in my vba?

 

[dmt32]

The transfer 1 procedure is in Module1. This is in Module 2. Should I have added the above procedure to Module 1 in my vba?

Share the actual code you are having issues with & explain in what way it is not working (any errors being reported)?

Dave

 

[Saurabhj]

Thank you for your reply Saurabh. The transfer 1 procedure is in Module1. This is in Module 2. Should I have added the above procedure to Module 1 in my vba?

The location of procedure (module1/ module2) will not impact the result until it's not marked as Private. Also what error are you getting ?

Please share the code of transfer1 also.

Thanks,
Saurabh

 

[bh123]

Share the actual code you are having issues with & explain in what way it is not working (any errors being reported)?

Dave

I have included the code in the main message above. Is there anything else you may need from me to help me?

The message says:
compile error:
Sub or Function not defined

many thanks for your reply

 

[dmt32]

I have included the code in the main message above. Is there anything else you may need from me to help me?

The message says:
compile error:
Sub or Function not defined

many thanks for your reply

The error is clear your Sub transfer1 either does not exist (or you have made it private) yet you stated in #post 3 that it is in module 1?

If you have copied transfer code from one module to another you need to rename it transfer1 and ensure that if it is being called from another module, that it is not private.

If still having issue - place copy of your workbook with dummy data on a file sharing site like dropbox & provide a link to it.

Dave

 

[bh123]

Meet Google Drive – One place for all your files

Google Drive is a free way to keep your files backed up and easy to reach from any phone, tablet, or computer. Start with 15GB of Google storage – free.

drive.google.com

I've never used this before! Hoping it works. Thank you for your support

 

[dmt32]

I am not a google user & not able access your file.

Dave