Good afternoon, I am trying to transfer information from my Excel User Form into the database table. After searching vigourously on the internet, I came across this formula to find the next empty row: - Private Sub cmdAdd_Click()
-
Dim iRow As Long
-
Dim ws As Worksheet
-
Set ws = Worksheets("DocumentFiled")
-
-
'Find First Empty Row in Database
-
iRow = ws.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row
I have the formula for transfering the information into the table: - 'Copy the data to the database
-
ws.Cells(iRow, 1).Value = frmDocument.DateCompleted.Value
-
ws.Cells(iRow, 2).Value = frmDocument.CaseNumber.Value
-
ws.Cells(iRow, 3).Value = frmDocument.coboDocTypes.Value
-
ws.Cells(iRow, 4).Value = frmDocument.DateFiled.Value
-
ws.Cells(iRow, 5).Value = frmDocument.coboNames.Value
And for emptying the User Form: - 'Clear the data for next entry
-
frmDocument.DateCompleted.Value = ""
-
frmDocument.CaseNumber.Value = ""
-
frmDocument.coboDocTypes.Value = ""
-
frmDocument.DateFiled.Value = ""
-
frmDocument.coboNames.Value = ""
-
-
End Sub
I cut and paste this formula into my VBA program; however I receive the following error:
Run-Time error '1004'
Application-defined or object-defined error
My question is what am I not seeing in this formula?
First and foremost Sigma, please review Before Posting (VBA or SQL) Code. It seems clear that there are a few problems here that should never be needed to be posted. You'll understand that better after reading the linked article. Don't worry. This is as much for your benefit as ours. There are tips in there that can help you avoid even the need to ask.
If you find that you still have problems after fixing all the items I see and point out, remember to include the line number of where the error occurs. Without this, we obviously will struggle to help you. First Batch : - Line #4 appears to have a misspelled reference. Probably should say :
- Set ws = Worksheets("DocumentField")
- Line #7 has either a typo or a misread reference.
x1Up <> xlUp . Second Batch :
Nothing drastically or obviously wrong here, but could be abbreviated to : - 'Copy the data to the database
-
With frmDocument
-
ws.Cells(iRow, 1) = .DateCompleted
-
ws.Cells(iRow, 2) = .CaseNumber
-
ws.Cells(iRow, 3) = .coboDocTypes
-
ws.Cells(iRow, 4) = .DateFiled
-
ws.Cells(iRow, 5) = .coboNames
-
End With
Third Batch :
Not sure where this code came from, but it's not too reliable (because some controls cannot take a string value). Try instead : - 'Clear the data for next entry
-
With frmDocument
-
.DateCompleted = Null
-
.CaseNumber = Null
-
.coboDocTypes = Null
-
.DateFiled = Null
-
.coboNames = Null
-
End With
-
End Sub
4 2597 NeoPa 32,556
Expert Mod 16PB
First and foremost Sigma, please review Before Posting (VBA or SQL) Code. It seems clear that there are a few problems here that should never be needed to be posted. You'll understand that better after reading the linked article. Don't worry. This is as much for your benefit as ours. There are tips in there that can help you avoid even the need to ask.
If you find that you still have problems after fixing all the items I see and point out, remember to include the line number of where the error occurs. Without this, we obviously will struggle to help you. First Batch : - Line #4 appears to have a misspelled reference. Probably should say :
- Set ws = Worksheets("DocumentField")
- Line #7 has either a typo or a misread reference.
x1Up <> xlUp . Second Batch :
Nothing drastically or obviously wrong here, but could be abbreviated to : - 'Copy the data to the database
-
With frmDocument
-
ws.Cells(iRow, 1) = .DateCompleted
-
ws.Cells(iRow, 2) = .CaseNumber
-
ws.Cells(iRow, 3) = .coboDocTypes
-
ws.Cells(iRow, 4) = .DateFiled
-
ws.Cells(iRow, 5) = .coboNames
-
End With
Third Batch :
Not sure where this code came from, but it's not too reliable (because some controls cannot take a string value). Try instead : - 'Clear the data for next entry
-
With frmDocument
-
.DateCompleted = Null
-
.CaseNumber = Null
-
.coboDocTypes = Null
-
.DateFiled = Null
-
.coboNames = Null
-
End With
-
End Sub
Thank you very much for the quick reply to my question. I will definitely read the link provided. However, line 7 is the culprit. I placed the <> in front of the code and it return a Syntax error. I literally copied this code from a youtube video "create a userform in excel part 3 of 3." Thanks for the other code abbreviations (awesome).
NeoPa 32,556
Expert Mod 16PB Sigma7:
However, line 7 is the culprit. I placed the <> in front of the code and it return a Syntax error.
I'm not sure I was clear enough. What I was saying is that you used a character '1' (one) in place of the 'l' (ell) which would have been the correct spelling. Maybe you did get it, but I didn't understand your response in that case. FYI: Such typos are easier to spot when the CODE tags are used. Never use a word processor to edit code as this sort of thing (and other similar problems) happens frequently in that situation.
Either way, I'm pleased to have helped :-)
Try to understand the following code.
I think that is what you are looking for. - Option Explicit
-
Dim ws As Worksheet
-
Dim iRow As Long
-
-
Private Sub UserForm_Initialize()
-
Set ws = Worksheets("DocumentFiled")
-
'Find First Empty Row in Database
-
iRow = FirstEmptyRow
-
'in order to avoid recalculate it each time is pressed "cmdAdd" button
-
End Sub
-
-
Private Sub cmdAdd_Click()
-
'Copy the data to the database
-
With ws
-
.Cells(iRow, 1) = CDate(Me.txtDateCompleted)
-
.Cells(iRow, 2) = CLng(Me.txtCaseNumber)
-
.Cells(iRow, 3) = CStr(Me.cmbDocTypes)
-
.Cells(iRow, 4) = CDate(Me.txtDateField)
-
.Cells(iRow, 5) = CStr(Me.cmbNames)
-
End With
-
-
'Prepare for the next record
-
'next empty row
-
iRow = iRow + 1
-
'Clear controls
-
With Me
-
.txtDateCompleted = Null
-
.txtCaseNumber = Null
-
.cmbDocTypes = Null
-
.txtDateField = Null
-
.cmbNames = Null
-
End With
-
End Sub
-
-
Private Function FirstEmptyRow() As Long
-
'Find the last column in database (assuming that columns has headers)
-
Dim C As Long, Cmax As Long '
-
Cmax = 1
-
Do Until IsEmpty(ws.Cells(1, Cmax + 1))
-
Cmax = Cmax + 1
-
Loop
-
'Find the really empty row in database (where ALL fields have empty values)
-
FirstEmptyRow = 1
-
Do
-
For C = 1 To Cmax
-
If Not IsEmpty(ws.Cells(FirstEmptyRow, C)) Then
-
GoTo NextRow
-
End If
-
Next C
-
Exit Do 'First empty row is found
-
NextRow:
-
FirstEmptyRow = FirstEmptyRow + 1
-
Loop
-
End Function
A more elegant solution (and a lot easier too) can be based on .ControlSource property for a control.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Allison Bailey |
last post by:
Hi Folks,
I'm a brand new Python programmer, so please point me in the right
direction if this is not the best forum for this question....
I would like to open an existing MS Excel spreadsheet...
|
by: Otie |
last post by:
I found the following under the GetObject help notes and in the
example for GetObject:
"This example uses the GetObject function to get a reference to a
specific Microsoft Excel worksheet...
|
by: Matthew Wieder |
last post by:
I have the following requirements:
Build a stand-alone C# application that asks the user to click in a cell
in an Excel spreadsheet, and then displays the address of that cell in
the C#...
|
by: pmud |
last post by:
Hi,
I need to use an Excel Sheet in ASP.NET application so that the users can
enter (copy, paste ) large number of rows in this Excel Sheet. Also, Whatever
the USER ENETRS needs to go to the...
|
by: Howard Kaikow |
last post by:
There's a significant problem in automating Excel from VB .NET.
Reminds me of a problem I encountered almost 3 years ago that was caused by
the Norton Auntie Virus Office plug-in.
Can anybody...
|
by: Anthony |
last post by:
To me, creating Excel 2003 spreadsheets programmatically via VB.NET hasn't
really changed since the days of VB6.
That is, I'd do something similar to this
Code:
Dim ExcelApp As...
|
by: Alain \Mbuna\ |
last post by:
Hi everybody.
In my program I have some data that is calculated after some input from the
user. I have written some code that opens an Excel workbook, with 5
worksheets and the calculated data...
|
by: alexia.bee |
last post by:
Hi all,
In some weird reason, excel instance won;t die if i remove the comment
from 4 lines of setting values into struct.
here is a snipcode
public...
|
by: Doug Glancy |
last post by:
I got the following code from Francesco Balena's site, for disposing of Com
objects:
Sub SetNothing(Of T)(ByRef obj As T)
' Dispose of the object if possible
If obj IsNot Nothing AndAlso...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
| |