[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