[RESOLVED] VBA and Access Database integration..help pls..??

[zach007]

Dear Experts,

I am trying to make a VBA program from a "word document" that can collect information/data from Ms Access database...my question is:

There are : Item Code , Descriptions, Qty, Unit Price and Total (all as TextBoxs except Item Code as a drop down menu)...what I am trying to do is, when I select Item Code from the drop down menu, it will collect all information (Description, Qty, Unit Price) and write into each TextBoxs and also it will calculate automatically for Total and write the total into the TextBox...

Anyone could help me please..please..???

Herewith, I attached a sample/dummy as a ZIP file for your reviews...and for my self learning...dont worry about other codes (such as how to write into the word documents, how to pop up, etc)....what I am trying to do is just how to make those codes run well....

Thank you very much for your kind attention and helps.

Best Regards,

Jennifer

[Ecniv]

Silly q's... But why are you coming from word to access, to put the data in a form?

you probably need to look at ADO. Open a connection object to the database, a recordset (using an sql statement) to get the data and loop through it putting it on to the forms fields.

Or if it is going into a mail merge, there might be a different option.

[DaveBo]

Here's an example of a VBA function that can open an Access DB, and load a recordset with data. Also an example of using the routine. Have fun.

Function Try_DB()
    ' Just an example of calling the Execute_SQL_ReturnRecordSet routine,
    ' loading a recordset and getting some data from it.
    
    Dim rst As ADODB.Recordset
    Dim x As Integer
    
    Set rst = Execute_SQL_ReturnRecordSet("Select * from Presidents", , _
        "G:\Access\Access_Play.mdb")
    
    For x = 1 To rst.RecordCount
        Debug.Print rst.Fields("Name").Value & " " & rst.Fields("DoB").Value
        
        rst.MoveNext
    Next
    
End Function
 
 
Function Execute_SQL_ReturnRecordSet(ByVal strQuery As String, _
    Optional ByVal UpDateable As Boolean = False, _
    Optional ByVal RemoteMDB As String = "") As ADODB.Recordset
 
    ' Requires reference to ActiveX Data Objects Library:
    '   Tools - References - check Microsoft ActiveX Data Objects xx Library
 
    ' This accepts an SQL query string or table name & returns a recordset
    ' call as:
    '   Dim rst As ADODB.Recordset
    '   Set rst = Execute_SQL_ReturnRecordSet ("Select * from Temp")
    ' To allow modifications to the underlying table set UpDateable=True
    ' To access a remote table in a different Access MDB file
    ' specify its path\name in RemoteMDB
 
    Dim con As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim rstErr As New ADODB.Recordset
 
    con.Errors.Clear
    On Error GoTo ErrBranch
 
    RemoteMDB = Trim(RemoteMDB)
    If RemoteMDB = "" Or UCase(RemoteMDB) = "CURRENTDB" Then
        'Set con = CurrentProject.Connection ' CurrentDB
    Else
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & RemoteMDB
        con.Open
    End If
 
    If con.State = adStateOpen Then
        Set rst.ActiveConnection = con
        rst.CursorType = adOpenKeyset
        If UpDateable Then
            rst.LockType = adLockOptimistic
        Else
            rst.LockType = adLockReadOnly
        End If
    End If
 
    rst.Open strQuery
    Set Execute_SQL_ReturnRecordSet = rst
 
    GoTo DoneSki
 
ErrBranch:
    ' the DB wasn't opened. Create a dummy recordset
    ' just so we can return something without erroring out.
    ' When checked, the resulting rst.RecordCount=0
    rstErr.Fields.Append "failed", adVarChar, 10, adFldMayBeNull
    rstErr.Open
    Set Execute_SQL_ReturnRecordSet = rstErr
 
DoneSki:
    Set rst = Nothing
    Set rstErr = Nothing
    Set con = Nothing
 
End Function    ' Execute_SQL_ReturnRecordSet
'#######################################################################

[zach007]

Hi,

Thanks for your reply.

Anyway...have you look into my vb form & my Ms Access please..???..because your example is really confusing me and did not solve my problem...and to ECNIV, yes it is...I just want to know how to get data from access and show it to each TextBoxs...and calculate the TOTAL....I really really NEW for VB Database...So, I really need helps...

Thanks,

Jennifer

[DaveBo]

I don't know how to get the form to display automatically, so I added a button to the document with the caption "Display The Form"

Now, go to the VBA code window, the "IDE", to get there quickly press Alt-F11

In the ThisDocument code section you'll see the code behind that button that simply opens your form:
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

I added the Execute_SQL_ReturnRecordSet function by:
- creating Module1 (Insert – Module)
- then pasting the function code into that

See the code in UserForm1 for where the combobox is loaded
and where it senses a selection to load the textboxes.

Hopefully this will get started.
Good luck, DaveBo

[zach007]

Hi Dave,

Thank you for your kind reply and helps...Now is getting close to what I mean...that was looks like the document on the web...my questions are:

How to make it on the Word Document instead of on the web view please? what code should I change for that?

Secondly, how to calculate total on the vb form please..?? what kind of code to be used for calculating into the total TextBox in the vb form please..???...

You dont have to worry to add all text into the Word template...once you let me know how to make "View" in the word document instead of as a website view, it will be great..!!

Thanks for your kind helps..

Jennifer

[DaveBo]

How to make it on the Word Document instead of on the web view please? what code should I change for that?

I'm not sure I understand. If you mean the "Normal View" vs. the "Web Layout View" in Word, just clicking on the desired icon near the bottom left of the Word window seems to work fine in either view.

Here's a simple example of calculating the total and putting it into the txttotal text box.
See the "' If txtqty & txtunit look like valid numbers" section in the middle of the routine below.

Private Sub cmb_item_Change()
    Dim ItemCode As String
    Dim x As Integer
 
    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
    
        ' The record should contain only 1 record right now
        If rst.RecordCount = 1 Then
            txtdes.Text = rst.Fields("Description").Value
            txtqty.Text = rst.Fields("Qty").Value
            txtunit.Text = rst.Fields("Unit_Price").Value
            
            ' If txtqty & txtunit look like valid numbers
            ' then calculate a Total and put into txttotal textbox
            If IsNumeric(txtqty) And IsNumeric(txtunit) Then
                'txttotal = txtqty * txtunit
                ' to fancy it up a bit you can do
                txttotal = Format$(txtqty * txtunit, "$#.00")
            Else
                txttotal = "?"
            End If
 
        ElseIf rst.RecordCount > 1 Then
            MsgBox "There seem to be too many records for Item_Code " & ItemCode
        Else
            MsgBox "There seem to be no records for Item_Code " & ItemCode
        End If
    End If
 
End Sub

Hope this helps. Merry Christmas & Happy Holidays, DaveBo

[zach007]

Hi Dave,

Thank you for your kind response and helps...Hmmm, I think I should explain like this:

When I select Item Code from its drop down menu, it will grab data from Ms Access database and write in each textbox for: "Description" and "Unit Price" only....then, we should put manually for "Qty"...but WHEN we "Tab" it will calculate the "Total"....

Would you please help me for this..??

Thanks many thanks for your helps..

Jennifer

[DaveBo]

Here's one way to do it.
This just detects a change in the qty textbox and recalculates the total each time.

Add this to your UserForm1 VBA code

Private Sub txtqty_Change()
    ' If txtqty & txtunit look like valid numbers
    ' then calculate a Total and put into txttotal textbox
    If IsNumeric(txtqty) And IsNumeric(txtunit) Then
        'txttotal = txtqty * txtunit
        ' to fancy it up a bit you can do
        txttotal = Format$(txtqty * txtunit, "$#.00")
    Else
        txttotal = "?"
    End If
End Sub