VBA one form two separate databases

[KristenLehman]

Hello,

I created an excel form that contains drop downs, and fill in sections. It has 4 clickable buttons "Save", "Modify", "Delete", and "Reset"

I have two different sheets one called "Database 1", and the other called "Database 2".

Currently when I click on save everything gets dumped into Database 1.

I added a drop down called location, and it contains "Location 1", and "Location 2".

What I'm trying to do is if I select "Location 2" within the form and I push save I want everything to get dumped into "Database 2", and when I select Location 1, everything gets dumped into "Database 1".

This is my current code for "Save" how would I go about writing the code for if location = Location 1 dump data into Database 1 etc?

Sub Save()

Dim frm As Worksheet
Dim database As Worksheet

Dim iRow As Long
Dim iSerial As Long

Set frm = ThisWorkbook.Sheets("Form")

Set database = ThisWorkbook.Sheets("Database 1")

If Trim(frm.Range("M1").Value) = "" Then

iRow = database.Range("A" & Application.Rows.Count).End(xlUp).Row + 1

If iRow = 2 Then
iSerial = 1

Else

iSerial = database.Cells(iRow - 1, 1).Value + 1

End If


Else

iRow = frm.Range("L1").Value
iSerial = frm.Range("M1").Value

End If

With database

.Cells(iRow, 1).Value = iSerial

.Cells(iRow, 2).Value = frm.Range("I11").Value
.Cells(iRow, 3).Value = frm.Range("I13").Value
.Cells(iRow, 4).Value = frm.Range("I15").Value
.Cells(iRow, 5).Value = frm.Range("I17").Value
.Cells(iRow, 6).Value = frm.Range("I19").Value
.Cells(iRow, 7).Value = frm.Range("I21").Value
.Cells(iRow, 8).Value = frm.Range("I23").Value
.Cells(iRow, 9).Value = frm.Range("I25").Value
.Cells(iRow, 10).Value = frm.Range("I27").Value
.Cells(iRow, 11).Value = frm.Range("I31").Value
.Cells(iRow, 12).Value = frm.Range("I33").Value
.Cells(iRow, 13).Value = frm.Range("I35").Value
.Cells(iRow, 14).Value = frm.Range("I7").Value
.Cells(iRow, 15).Value = [Text(Now(), "DD-MM-YYYY HH:MM:SS")]

End With

frm.Range("L1").Value = ""
frm.Range("M1").Value = ""

 

[dmt32]

Hi,
with the following assumptions
- Form is a worksheet
- Save code is located in the Form worksheets code page
- Combobox is an ActiveX control

try following & see if does what you want

Sub Save()
    
    Dim frm         As Worksheet
    Dim Database    As Worksheet
    
    Dim iRow        As Long
    Dim iSerial     As Long
    Dim Index       As Long
    
    Set frm = ThisWorkbook.Sheets("Form")
    
    
    Index = Me.Location.ListIndex + 1
    If Index = 0 Then Exit Sub
    
    Set Database = ThisWorkbook.Sheets("Database " & Index)

'rest of code

Dave

 

[Askil]

Hello,

I created an excel form that contains drop downs, and fill in sections. It has 4 clickable buttons "Save", "Modify", "Delete", and "Reset"

I have two different sheets one called "Database 1", and the other called "Database 2".

Currently when I click on save everything gets dumped into Database 1.

I added a drop down called location, and it contains "Location 1", and "Location 2".

What I'm trying to do is if I select "Location 2" within the form and I push save I want everything to get dumped into "Database 2", and when I select Location 1, everything gets dumped into "Database 1".

This is my current code for "Save" how would I go about writing the code for if location = Location 1 dump data into Database 1 etc?

Sub Save()

Dim frm As Worksheet
Dim database As Worksheet

Dim iRow As Long
Dim iSerial As Long

Set frm = ThisWorkbook.Sheets("Form")

Set database = ThisWorkbook.Sheets("Database 1")

If Trim(frm.Range("M1").Value) = "" Then

iRow = database.Range("A" & Application.Rows.Count).End(xlUp).Row + 1

If iRow = 2 Then
iSerial = 1

Else

iSerial = database.Cells(iRow - 1, 1).Value + 1

End If


Else

iRow = frm.Range("L1").Value
iSerial = frm.Range("M1").Value

End If

With database

.Cells(iRow, 1).Value = iSerial

.Cells(iRow, 2).Value = frm.Range("I11").Value
.Cells(iRow, 3).Value = frm.Range("I13").Value
.Cells(iRow, 4).Value = frm.Range("I15").Value
.Cells(iRow, 5).Value = frm.Range("I17").Value
.Cells(iRow, 6).Value = frm.Range("I19").Value
.Cells(iRow, 7).Value = frm.Range("I21").Value
.Cells(iRow, 8).Value = frm.Range("I23").Value
.Cells(iRow, 9).Value = frm.Range("I25").Value
.Cells(iRow, 10).Value = frm.Range("I27").Value
.Cells(iRow, 11).Value = frm.Range("I31").Value
.Cells(iRow, 12).Value = frm.Range("I33").Value
.Cells(iRow, 13).Value = frm.Range("I35").Value
.Cells(iRow, 14).Value = frm.Range("I7").Value
.Cells(iRow, 15).Value = [Text(Now(), "DD-MM-YYYY HH:MM:SS")]

End With

frm.Range("L1").Value = ""
frm.Range("M1").Value = ""

did it work ? i have the same "problem"