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

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 ;)

Hi,

Use the following code in your user form:

VB Code:

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.

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

VB Code:

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

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 ;)

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

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.... :confused:

Thanks for all your kind helpfulness...

Best Regards,

Jennifer :)

Check out the uploaded file.

the date format should be like this:

VB Code:

'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

Quote:

---

Originally Posted by Hack

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

VB Code:

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

---

Oops!

the date format in your code is wrong!

should be

VB Code:

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

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...

VB Code:

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

:D

Hi,

I put this code into "ThisDocument"

VB Code:

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.... :(

In the Document_Open event?

Hi,

I did this:

VB Code:

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.

:confused:

Does the bookmark exist in that document?

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....:)

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

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..???... :confused:

Please find my attached file....

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

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.

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

Oh, missed that part. I'll look.

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

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.

VB 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"
    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

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".....:(

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

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

VB 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"
    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

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:

VB Code:

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 +'..???

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

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....

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

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.... :)

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.

Hi,

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

VB Code:

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....

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

VB Code:

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

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.

Hi,

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

VB 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"
    
         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....

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.

VB Code:

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

Hi,

I did this codes, but still errors:

VB 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...???.... :confused:

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.

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

Thanks very much....

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

Dear Experts,

Has anyone could help me please for these code?

VB 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... :wave:

Thank you so much...

Your going to have to move the rst recordset to whatever row your focussing on. Your going to have to loop until your rst is on the table cell that has the focus or is selected.

Please...show me the code (the corrected codes please)..because that is better for me to learn....I am just a novice user and try to learn how to make it...because there is no on the net and also on the bookas far as I did the search...I only hope from this forum.....so, please me the codes if you dont mind....anyway, this is also for everybody in this forum who would like to create kind of this form...:) :thumb:

Thank you for your kindness...

Jennifer ;)

Dear Experts,

I really need your help with this codes...I tried many time and this is my last codes but still wont work...please help me:

VB 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"
    If Not TypeName(rst) = "Nothing" Then
        'something
        Dim ItemCode As String
          
         rst.MoveFirst
Do While rst.EOF = False
    ItemCode = Trim$(cmb_item)
 
    If ItemCode <> "" Then
        ' Restrict/filter the recordset by the selected Item_Code
        Debug.Print "Item_Code=" & ItemCode
        rst.Filter = "Item_Code = " & ItemCode
    
    End If
    
    If UserForm1.txtqty.Text = rst.Fields("Item_Code").Value Then
    
    Exit Do
    rst.MoveNext
Loop
        If rst.State = adStateOpen Then
            'open
            rst.Close
            Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", , _
        "C:\exercise.mdb")
        Else
            'closed
            Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", , _
        "C:\exercise.mdb")
        End If
    Else
        'nothing
        Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", , _
        "C:\exercise.mdb")
    End If
    TextBox3.Text = rst.Fields("Qty")
 
End Sub

I am very appreciate if you make a correction for this code to make it run...by only giving advice could not solve the problem because I know the logic BUT i dont know what should I put on first (the code) and what next and what next...before I see the sample...example...etc...about how to write its codes...so, please be understood.....

Thanks a lot for your kind helps... :confused:

Hi Rob,

Sorry...would you like to help me to find the code please..???

Thanks very much for your kindness..

Jennifer :(

Please help me with this codes...only one step to finish...thanks a lot,

Jennifer :(

You need to compare the item id with the item id and not qty.

VB Code:

If UserForm1.txtqty.Text = rst.Fields("Item_Code").Value Then
 
'Should be - Item_Code or whatever the txt name is.
If UserForm1.txtItem_Code.Text = rst.Fields("Item_Code").Value Then

Hi,

I tried but still error:

VB Code:

Private Sub cmd_CheckCurrent_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
        Dim ItemCode As String
                   rst.MoveFirst
Do While rst.EOF = False
    ItemCode = Trim$(cmb_item)
 
    If ItemCode <> "" Then
        ' Restrict/filter the recordset by the selected Item_Code
        Debug.Print "Item_Code=" & ItemCode
        rst.Filter = "Item_Code = " & ItemCode
    
    End If
        If UserForm1.cmb_item.Text = rst.Fields("Item_Code").Value Then
Loop
    rst.MoveNext
 
        If rst.State = adStateOpen Then
            'open
            rst.Close
            Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", , _
        "C:\exercise.mdb")
        Else
            'closed
            Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", , _
        "C:\exercise.mdb")
        End If
    Else
        'nothing
        Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", , _
        "C:\exercise.mdb")
    End If
  
 
End Sub

If its not matching with the value in the database you need to check the values manually. Do a Debug.Print of each value to be compared, combo and database. If the combo's value has no trailing spaces then it should be jsut the word. If the value in the database, more then likely, has trailing spaces then the two items will not match.

Also what is the type for the db field?

Hi,

I am using ADO, and the database is Ms Access........is there any tutorial/how to now the codes look like for this kind of form in Ms Words please...???...like auto increment number, line this kind questions I asked you...etc????..Is possible to have a look by looking from Macro record..???

Thanks,

Jennifer :)

We have a Database FAQ located here - http://www.vbforums.com/showthread.php?t=337051

Hi Rob,

I read trough all related FAQ but there is no answer or example there....especially for "Check Current Stock"...is it possible for me to know that codes please...because I intent to put all codes and file once the form is already done in VBFORUMS tutorial...;)

Hi Rob,

Sorry.. I just wanna ask...why dont you just fix that codes please..???...That is not only for me...Because you could post this issue of Word VBA on the tutorial so everybody can see it and learn from codes how to build that form, etc...and this forum will be the total solution forum with complete example...since other forums are not so good as VBFORUMS....;)