Database Userform

[ikalemi]

Hello, I am trying to create a userform for a database that repeats information in rows as I enter other information like Category and amount. As an example, see below table:

Project (CC1)	Funding Agency	Grant Name (CC3)	Contract Number	Start Date	End Date	Status	Category (Account)	Amount	Voucher Frequency
1000	DYCD	DYCD Kids	4441	1/1/2017	6/30/2017	Not Reg	8310: Travel	$ 5,445.00	Month
1000	DYCD	DYCD Kids	4441	1/1/2017	6/30/2017	Not Reg	7240 : salary	$ 12,000.00	Month
1000	DYCD	DYCD Kids	4441	1/1/2017	6/30/2017	Not Reg	7545: Client Transition Fund	$ 552.00	Month
1000	DYCD	DYCD Kids	4441	1/1/2017	6/30/2017	Not Reg	7580: Professional services	$ 2,333.00	Month
1300	HRA	HRA !!	2243	7/1/2016	6/30/2017	Final	7240 : salary	$ 5,000.00	Quarter
1300	HRA	HRA !!	2243	7/1/2016	6/30/2017	Final	7240 : salary	$ 25,000.00	Quarter
1300	HRA	HRA !!	2243	7/1/2016	6/30/2017	Final	7582: Sub-Contractors	$ 45,454.00	Quarter

Project would be a combo box with a dropdown list with all the project.
Grant name would be a textbox.
Start date would be a text box.
Same with End Date.
Status would be a combo box with a dropdown list.
Category is a combobox with dropdown list.
Amount textbox.
Voucher frequency is combobox with a dropdown list.

I apologize in advance if I am not clear in my explanation.

thank you

[Logit]

.
Here is an example of a basic database that you can work from :

Option Explicit


Sub mdSubmit_Click()
Dim wsBranch As Worksheet
Dim cNum As Integer
Dim X As Integer
Dim newRow As Long


'set the variable for the sheet
Set wsBranch = Worksheets(ComboBox1.Value)


'check for values
    If Me.ComboBox1.Value = "" Then
        MsgBox "Select a sheet from the combobox"
        Exit Sub
    End If
    
With wsBranch
    newRow = Application.WorksheetFunction.CountA(wsBranch.Range("A:A")) + 1
    
    wsBranch.Cells(newRow, 1).Value = txtName.Value
    wsBranch.Cells(newRow, 2).Value = txtFatherName.Value
    wsBranch.Cells(newRow, 3).Value = txtMotherName.Value
    wsBranch.Cells(newRow, 4).Value = ComboBox1.Value
    
    Unload Me
        
    'communicate the results
    MsgBox "The values have been sent to the " & ComboBox1.Value & " sheet"
End With

End Sub


Private Sub UserForm_Initialize()


    With ComboBox1
       .AddItem "Sheet1"
       .AddItem "Sheet2"
       .AddItem "Sheet3"
    End With


End Sub


Sub cmdSubmit_Click()


Dim wsBranch As Worksheet
Dim NextRow As Long


    If ComboBox1.ListIndex = -1 Then
        MsgBox "Please select a branch."
        Exit Sub
    Else
        Set wsBranch = Worksheets(ComboBox1.Value)
    End If


    With wsBranch
        NextRow = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row
        .Range("A" & NextRow).Resize(, 3).Value = Array(txtName.Value, txtFatherName.Value, txtMotherName.Value)
    End With


End Sub

[ikalemi]

Thank you for the quick reply Logit . I will work based on it.

[OBP]

This is an Access Forum and Logit has offered an Excel option, did you actually want an Access solution?

[Logit]

.
My apologies .. did not notice this was Access. Disregard my post.

[OBP]

Logit, I wasn't getting at you, I just wasn't sure from the OP's response whether they realised it was Access and is that what they wanted?

[Logit]

OBP:

I understand. No problem. I just didn't notice it was Access, otherwise I would have passed by the thread.

Thanks !

[ikalemi]

sorry all, I just noticed it was access too. It was meant to be in the excel forum.

[OBP]

No problem guys.