PDA

View Full Version : Database Userform



ikalemi
07-03-2017, 07:51 AM
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
07-03-2017, 09:12 AM
.
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
07-03-2017, 11:21 AM
Thank you for the quick reply Logit . I will work based on it.

OBP
07-05-2017, 06:44 AM
This is an Access Forum and Logit has offered an Excel option, did you actually want an Access solution?

Logit
07-05-2017, 06:55 AM
.
My apologies .. did not notice this was Access. Disregard my post.

OBP
07-05-2017, 07:10 AM
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
07-05-2017, 07:51 AM
OBP:

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

Thanks !

ikalemi
07-05-2017, 10:58 AM
sorry all, I just noticed it was access too. It was meant to be in the excel forum.

OBP
07-05-2017, 11:35 AM
No problem guys.