data store in access ..

[shahcu]

I have a user form that contains One Check box that has two text boxes in front of it a) Tentative date b) Actual Date. Looks like similar to one given below:


?Tentative date? ?Received on?



Now say when user selects Loan numbr ?LN001? from combo box given above. We need to check the address box check box.. as soon as we clicked on it tentative date comes up on its own? and we need to enter the "Received on" date when I actually receives the data.

Now when I click on command button the tentative date will get store in sheet 1 and Actual date will get store In sheet2 along with the value available in combo box. also want that when I enter the received date later on the loan number will not get store again in excel sheet just update the values in exsiting sheet..This is the code that I have mentioned.. but its not maintaining the sequence of data store in excel.. Pls correct me

I

Private Sub CommandButton1_Click()
Dim r As Long
r = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 1

ActiveWorkbook.Activate
Sheets("TAT").Select

ActiveSheet.Cells(r, 1) = ComboBox1.Value
ActiveSheet.Cells(r, 2) = TextBox1.Value
ActiveSheet.Cells(r, 3) = TextBox5.Value


ActiveWorkbook.Activate
Sheets("Received").Select

If TextBox2.Value = "" Then
?ActiveSheet.Cells(r, 1) = ""
Else
ActiveSheet.Cells(r, 1) = ComboBox1.Value
ActiveSheet.Cells(r, 2) = TextBox2.Value
End If

If TextBox6.Value = "" Then
?ActiveSheet.Cells(r, 1) = ""
Else
ActiveSheet.Cells(r, 1) = ComboBox1.Value
ActiveSheet.Cells(r, 3) = TextBox6.Value
End If

[shahcu]

Please see the form1.zip to get a better understanding of the form.

[shahcu]

Pls help ...

[Bob Phillips]

I guess most are like me, thye don't understand. Your word doc doesn't help a lot, a workbook would be more useful.

[shahcu]

We have 1 combo box , 1 check box and two text boxes.

Now when user select Loan number from combo box then he need to specify the deadline by which he is going to receive the Address proof of the applicant. SO one is tentative date another one is actual received date.

We have two worksheets a) TAT b) Received..

When user clicks on command button the tentative date needs to store in TAT worksheet alongwith the loan number.

Say applicant applied for loan today and tentative date is somewhat March30, 2007. So we only need to enter tentative date and not the received date. In case the applicant submitted the documents on May 29, 2007 then we need to enter received date in Received sheet. However loan number LN001 is already exist so what I want is it only updates the received worksheet and not the TAT worksheet. In case if the loan number exists then it will only modify the TAT and received dates and not the loan number in TAT and Received worksheets. For better understanding pls see the attachment.

[shahcu]

Pls help

[geekgirlau]

Try the following - I've renamed the text boxes and combo box on the form to make it simpler to follow.


Private Sub CommandButton1_Click()
Dim wks As Worksheet

' has a Loan Number been selected?
If Me.cboLoan <> "" Then
' has a tentative date been entered?
If Me.txtTentative <> "" Then
EnterDates Worksheets("TAT"), Me.txtTentative, Me.cboLoan
End If

' has a received date been entered?
If Me.txtReceived <> "" Then
EnterDates Worksheets("Received"), Me.txtReceived, Me.cboLoan
End If
End If
End Sub
Sub EnterDates(wks As Worksheet, strDate As String, strLoan As String)
Dim r As Long


' does the loan already exist?
On Error Resume Next
r = Cells.Find(What:=strLoan, LookIn:=xlFormulas, LookAt:=xlWhole).Row
' loan number not found
If Err.Number <> 0 Then
r = wks.Range("A" & Rows.Count).End(xlUp).Row + 1
End If

wks.Cells(r, 1) = strLoan
wks.Cells(r, 2) = Format(strDate, "dd/mm/yyyy")
End Sub

[shahcu]

Thank u very much for ur help.. But what i sent earlier is the sample data... actually i have to check values in 20 textboxs for tentative date and 20 textboxes for received date...

What I mentioned earlier was name of one document ie. Address proof but I have a list of total 20 documents "Address Proof" is jist one of it...

so the actual for contains...

1 combo box
20 check boxes for douments name
20 text boxes for tentative dates
20 text boxes for received date...
1 command button...

Pls help

Regards

[geekgirlau]

Why do you need 20 text boxes for tentative dates etc.?

[shahcu]

Actually I m preparing a form for loan application and in order to get the loan processed there are different documents which are required as per loan type. So there are in all 20 different document.. that may require for one or the other loan type.

[geekgirlau]

Surely you could simplify this by having a combo box for Loan Type, a combo box to select the document, 1 text box for the tentative date, and 1 text box for the received date. Having 20 text boxes on the one user form is certainly not to be recommended.

[shahcu]

I cannot to that bcoz of the project requirement. i have to check values in 20 textboxs for tentative date and 20 textboxes for received date...

so the actual form contains...

1 combo box
20 check boxes for douments name
20 text boxes for tentative dates
20 text boxes for received date...
1 command button...

Pls help

Regards

[geekgirlau]

If you want to have a large, messy user form with unnecessary code, you can certainly adapt my earlier sample to include the additional controls - just copy the code 19 more times and change the name of the controls accordingly.

However, despite your assertion that this is part of your requirements, I would suggest that you check to see what is really required as opposed to what they've asked for.

From what I understand, you need to record a running list of the tentative and received dates for up to 20 documents, for any number of loan numbers. If the date changes, your list needs to be updated accordingly. If it's a new entry, that should be added to the bottom of the list.

I've attached a new sample that has a list box with 20 document types. The list is multi-select, so you can hold down [Ctrl] to select more than 1 document type. When you select a loan number, any number of document types and enter either a tentative or received date (or both), the code will update your list.

Please test this and let me know if this is what you're after.

[shahcu]

I really appreciate ur assiatnce, hard work and time.

I will give it a try and get back to u if the issue persists.