Rank Newbie here.
I've initialized a database area in a worksheet, and using
Edit-Define, called the area "database", and am trying to code a
custom form in VBA to insert the data from the form to the database. I
want to add a new record at the bottom of the database list. I don't
know how to code this, or find help on it. I've done lots of this in
SQL but never in VBA to an Excel database/list. Can I have some sample
code for this?

PS. I don't want to use an automatic form (Data-Forms) because a
"current date" field is in the database and I want to generate it
automatically for the user.

This assumes that the UF has a text box into which the new entry is typed
named "TextBox1" and that there is a command button named "CommandButton1".
This command button is clicked to add the new entry to the end of the db. The
db is assumed to be in column A of "Sheet1" and all cells below the db in
column A are empty. Therefore, the cell identity for the new entry is
determined by finding the last entry in column A and offsetting 1 cell down.
Note that the named range is therefore unnecessary.

Paste the following to the UF code module:

Dim c As Range
Private Sub UserForm_Activate()
GetNewEntryAddress
End Sub

Private Sub CommandButton1_Click()
c.Value = TextBox1.Text
GetNewEntryAddress
End Sub

Private Sub GetNewEntryAddress()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
Set c = ws.Cells(Rows.Count, 1).End(xlUp)(2)
End Sub

" wrote:

Rank Newbie here.
I've initialized a database area in a worksheet, and using
Edit-Define, called the area "database", and am trying to code a
custom form in VBA to insert the data from the form to the database. I
want to add a new record at the bottom of the database list. I don't
know how to code this, or find help on it. I've done lots of this in
SQL but never in VBA to an Excel database/list. Can I have some sample
code for this?

PS. I don't want to use an automatic form (Data-Forms) because a
"current date" field is in the database and I want to generate it
automatically for the user.

wrote:
Rank Newbie here.
I've initialized a database area in a worksheet, and using
Edit-Define, called the area "database", and am trying to code a
custom form in VBA to insert the data from the form to the database. I
want to add a new record at the bottom of the database list. I don't
know how to code this, or find help on it. I've done lots of this in
SQL but never in VBA to an Excel database/list. Can I have some sample
code for this?

PS. I don't want to use an automatic form (Data-Forms) because a
"current date" field is in the database and I want to generate it
automatically for the user.

Assume the following

Insert a new worksheet
Name it Cust
Add 5 Field Headings in A1:E1
In the NameDefine Box enter a new name of DB
in the refers to box enter
=OFFSET(Cust!$A$1,0,0,COUNTA(Cust!$A:$A),5)

Create a new userform
Create 5 textboxes named Textbox1 to Textbox5
Create 2 Command Buttons
First button give a caption of Close and name it CmdClose
Second button give a caption of Add and name it CmdAdd

In the userform add the following code

Option Base 1
Dim NumOfFields As Integer
Dim Ar() As Variant
Dim Dbdata As Range

Private Sub UserForm_Initialize()
Set Dbdata = Range("Db")
NumOfFields = Dbdata.Columns.Count
ReDim Ar(NumOfFields)
End Sub

Private Sub CmdAdd_Click()
Dim I As Integer
Dim NewRecord As Range

Set Dbdata = Range("Db")
For I = 1 To NumOfFields
With Me.Controls("TextBox" & I)
Ar(I) = .Value
.Value = ""
End With
Next
Set NewRecord = Dbdata.Offset(Dbdata.Rows.Count, 0).Resize(1, 5)
NewRecord.Value = Ar()
End Sub

Private Sub CmdClose_Click()
Unload Me
End Sub

Display the userform add data into the textboxes and click
the Add button

thanks, guys!

I'll try out the code. Nice to have people respond this way.