[RESOLVED] How to set current date..??

[zach007]

Dear Experts,

I am trying to make this following shymptoms:

When I open this word document (current_date.doc), the form will automatically open and also automatically fill the current date with time in TextBox1....anyone could help me please to perform like this..???

Please find my attached file for your reviews.

Thanks very much for your helps.

Best Regards,

Jennifer

[cssriraman]

Hi,

Use the following code in your user form:

Private Sub UserForm_Initialize()
TextBox1.Text = Date
'If you want to change the format of the date use format function
'TextBox1.Text = Format(Date, "DD-MMM-YYYY, HH:mm")
'If you want to display time with the format:
'TextBox1.Text = Format(Time, "hh:mm:ss")
'If you want to display both date and time you can use now function
'TextBox1.Text = Format(Now(), "DD-MMM-YYY hh:mm")
End Sub

_____________
CS.

[Hack]

One minor note. You do not need the parentheses with Now.

Text1.Text = Format(Now, ""DD-MMM-YYY hh:mm")

[zach007]

Hi All,

Thanks very much for your helps...However, I tried to use your codes but the year was not 2005...it is 3451 or something...what was wrong with it..???..Additionally, I can still write on the TextBox...How should I do in order me could not write anything/re-write on the TextBox1 please???.....Please find my attached file and have a look what was wrong with my codes please...

Thank you for your kind helps...

Best Regards,

Jennifer

[salvelinus]

What's the date format on your system? That looks like a Chinese or Jewish year, except they're something like 4702 & 5766, respectively.

[zach007]

Hi All Experts,

Have you open my file and look at it please..???...I just want to make sure the year will be 2005...not like this. i.e. 8-Dec-05342 8:48......Additionally, I can still write up on the TextBox1...what I would like trying to do is when the document is open, the form will automatically come up and show the current date and time on the TextBox1 and anyone could not changed/rewrite the date and time on that TextBox1 (it is looked like a disable for rewriting)...

Anyone could help me for that please...please....

Thanks for all your kind helpfulness...

Best Regards,

Jennifer

[cssriraman]

Check out the uploaded file.

the date format should be like this:

'To set the date and time in Textbox when loading the form
TextBox1.Text = Format(Now, "DD-MMM-YYYY hh:mm")
 
'not as
'TextBox1.Text = Format(Now, "DD-MMM-YYY hh:mm")
 
'To restrict the user not to write in text box
TextBox1.Locked = True

[cssriraman]

One minor note. You do not need the parentheses with Now.

Text1.Text = Format(Now, ""DD-MMM-YYY hh:mm")

Oops!

the date format in your code is wrong!

should be

Text1.text =  Format(Now, "DD-MMM-YYYY hh:mm")

[zach007]

Dear Experts,

What additional code should I put if I would like to put a current date on bookmark directly rather than on TextBox....For an example, if I open my word document .i.e. testing.doc, it will automatically show current date on the bookmark....

Anyone could help me please...???

Thank you so much...

[RobDog888]

ActiveDocument.Bookmarks.Item("CurrentDate").Range.Text = Format(Now, "DD-MMM-YYYY hh:mm")

[zach007]

Hi,

I put this code into "ThisDocument"

ActiveDocument.Bookmarks.Item("bk_date").Range.Text = Format(Now, "DD-MMM-YYYY hh:mm")

But still does not show up automatically when I open that word doc....

[RobDog888]

In the Document_Open event?

[zach007]

Hi,

I did this:

Private Sub Document_Open()
 
ActiveDocument.Bookmarks.Item("bk_date").Range.Text = Format(Now, "DD-MMM-YYYY hh:mm")
 
End Sub

and received an error message: Run Time Error: '6028' The range cannot be deleted.

[RobDog888]

Does the bookmark exist in that document?

[zach007]

Yes, it is exist. When I save it. Then I re-open the word document then there is an error message: Run Time Errors '6028' - The range cannot be deleted.

Please help me for this....

[RobDog888]

Attach your most current document and I'll look at it.

[zach007]

Hi,

Thanks a lot for your helps...it is on section "Date of Issue" in word document...I make the bookmark invisible...so, you could double click on it to see the bookmark name which is "bk_date"....

Additionally, on UserForm1, on Update Item tab, there is a "Check Current Stock" button...why with this button, I have to press it twice or more to get current data result..???..after I add stock....Would you please also help me for this..???...

Please find my attached file....

Thank you very much for your kind helps....

[RobDog888]

Ok, I got it. Its giving that error from the bookmark being a Date format. you need to set the format of it from the properties context menu item when the bookmark is selected from the goto bookmark via the Insert > Bookmarks... dialog.

Then type in the custom format that you have and it will work.

[zach007]

It works...!!!...Thanks a lot for your kind helps...???....How about the button that I asked to you..??

[RobDog888]

Oh, missed that part. I'll look.

You could also format the bookmark as a Text field instead of a Date field too.

[RobDog888]

Looks like your not refreshing your rst. Sorry if its a bit off as I'm tired and getting ready to call it a night.

Private Sub CommandButton3_Click()
 
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\exercise.mdb"
    If Not TypeName(rst) = "Nothing" Then
        'something
        If rst.State = adStateOpen Then
            'open
            rst.Close
            Set rst = Execute_SQL_ReturnRecordSet
        Else
            'closed
            Set rst = Execute_SQL_ReturnRecordSet
        End If
    Else
        'nothing
        Set rst = Execute_SQL_ReturnRecordSet
    End If
    TextBox3.Text = rst.Fields("Qty")
 
End Sub

[zach007]

Hi,

That all right..thanks very much anyway...But, when those codes run, then it has a "compile error : Argument not optional" when at go to this part "Set rst = Execute_SQL_ReturnRecordSet".....

[RobDog888]

Oh, sorry. You need to pass the arguments to your function. I wasnt too familiar with your funciton.

[RobDog888]

Ok, I think this should do it. I was just going by your examples.

Private Sub CommandButton3_Click()
 
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\exercise.mdb"
    If Not TypeName(rst) = "Nothing" Then
        'something
        If rst.State = adStateOpen Then
            'open
            rst.Close
            Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", False, "C:\exercise.mdb")
        Else
            'closed
            Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", False, "C:\exercise.mdb")
        End If
    Else
        'nothing
        Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", False, "C:\exercise.mdb")
    End If
    TextBox3.Text = rst.Fields("Qty")
 
End Sub

[zach007]

Hi,

Sorry for that...but why NOW is getting worst....when I click "Check Current Stock", it always show the result "14"....

and the worst is..when I click "Add Stock" button...before, it runs well without errors, now it has errors:

Private Sub cmdAddStock_Click()
 
Dim con As ADODB.Connection
Set con = New ADODB.Connection
  con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
          & "c:\exercise.mdb"
 
 
    'update the data
    Dim strSQL As String
        strSQL = "UPDATE item " _
            & " SET Qty = Qty + " & TextBox5.Text _
            & " WHERE Item_Code = " & Trim$(cmb_item)
        con.Execute strSQL
        
               MsgBox "Your stock has been updated.", , "Update Stock Success"
               TextBox3.Text = rst.Fields("Qty")
        
            
        
        Application.ScreenRefresh
 
End Sub

and "con.Execute strSQL was yellow highlighted and errors: Syntax error (missing operator) in query expression 'Qty +'..???

[RobDog888]

Probably because of the sql querystring. Not sure what your needing but this requeries the table and reloads the rst.

[zach007]

Hi,

It works for "Add Stock" button..that my mistakes...I forgot to put something there...BUT with "Check Current Stock" button, I really have no idea what is going on...it suppose to show current updated stock...BUT it keep show "14" which is the first row qty on the Item table...would you please have a look at my form and click it on "Check Current Stock" please...???...please....???....or if you are tired, you could check it tomorrow...I really stack......have you still got my file...????

I really need your help...

Thanks a lot....

[RobDog888]

14 would be the first record. Whats its supposed to show?

[zach007]

It suppose to show on selected Item_code, then it will show its "Qty".....i.e. If I select Item_Code number 3, then it will show its qty....please help me for this code....

[RobDog888]

Ok, then you will want to just move the recordset position to that record where the Item_Code matches the current Item_Code in the selected row on the form. You can do it with a loop and an rst.MoveNext with the test for match after that.

[zach007]

Hi,

I tried many times...but still did not works...this is my last codes for "Check Current Stock":

Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\exercise.mdb"
    
      Dim x As Integer
      Dim ItemCodes(10)
       
 
       For x = 1 To rst.RecordCount
        ItemCodes(x - 1) = rst.Fields("Item_Code").Value
        Debug.Print rst.Fields("Item_Code").Value & " " & _
            rst.Fields("Description").Value
    
    If Not TypeName(rst) = "Nothing" Then
        'something
        If rst.State = adStateOpen Then
            'open
            rst.Close
            Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", False, "C:\exercise.mdb")
            
        Else
            'closed
            Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", False, "C:\exercise.mdb")
        End If
    Else
        'nothing
        Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", False, "C:\exercise.mdb")
    End If
    TextBox3.Text = rst.Fields("Qty")
 
      rst.MoveNext
    Next

Could you please fix my code please....????...Because I dont know what should I do anymore....

Thank you so much....

[RobDog888]

No, you cant just do a .MoveNext without evaluating which record the recordset is on.

rst.MoveFirst
Do While rst.EOF = False
    If UserForm1.txtQty.Text = rst.Fields("Item_Code").Value Then
        Exit Do
    End f
    rst.MoveNext
Loop

[RobDog888]

Step through the code and see if your values are what you should be getting. Press F8 to step through the code after you place a breakpoint on Command3_Click and click the button.

[zach007]

Hi,

I did this codes and follow your codes...but still error:

Private Sub CommandButton3_Click()
 
 Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\exercise.mdb"
    
         rst.MoveFirst
Do While rst.EOF = False
    If UserForm1.txtqty.Text = rst.Fields("Item_Code").Value Then
        Exit Do
    End If
    rst.MoveNext
Loop
TextBox3.Text = rst.Fields("Qty")
        
    Application.ScreenRefresh
End Sub

and "TextBox3.Text = rst.Fields("Qty")" was in yellow highlighted...and error message like this: Run Time Error '3021' Either BOF is TRUE, or the current record has been deleted. Requested operation requires a current records.

Would you please correcr my codes...????..

Thank you so much....

[RobDog888]

I dont see where your opening your recordset rst? I had it in the code I posted. This is why your probably getting the 3021 error.

Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", False, "C:\exercise.mdb")

[zach007]

Hi,

I did this codes, but still errors:

Private Sub CommandButton3_Click()
 
 Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\exercise.mdb"
    
    Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", False, "C:\exercise.mdb")
    
         rst.MoveFirst
Do While rst.EOF = False
    If UserForm1.txtqty.Text = rst.Fields("Item_Code").Value Then
       Exit Do
    End If
    rst.MoveNext
Loop
 
    TextBox3.Text = rst.Fields("Qty")
        
    Application.ScreenRefresh
End Sub

When I select item no 1, then when I press "Check Current Stock" it show its Qty...BUT, when I select other item numbers, let say item code number 3 it still show the Qty for item number 1...???....

[RobDog888]

My mistake. We are not comparing the correct fields. We need to cpmpare the Item_Code field with whatever control holds that value on the userform.

[zach007]

Could you please show me the code please..???..Because I really have no idea how to do that...

Thanks very much....

[zach007]

Please help me for this codes...?????

[zach007]

Dear Experts,

Has anyone could help me please for these code?

Private Sub CommandButton3_Click()
 
 Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\exercise.mdb"
    
    Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", False, "C:\exercise.mdb")
    
         rst.MoveFirst
Do While rst.EOF = False
    If UserForm1.txtqty.Text = rst.Fields("Item_Code").Value Then
       Exit Do
    End If
    rst.MoveNext
Loop
 
    TextBox3.Text = rst.Fields("Qty")
        
    Application.ScreenRefresh
End Sub

When I select item no 1, then when I press "Check Current Stock" it show its Qty...BUT, when I select other item numbers, let say item code number 3 it still show the Qty for item number 1...???....

Please help with code corrections...

Thank you so much...