Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
hi
i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
Use a userform that contains a combobox or list box that is populated with
data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
hi Doug,
thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
Use a Combobox on the form that is populated with data from the database and
then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
thanks doug,
what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
You should look at the links in my initial response to this thread.
Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
hi Doug,
thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
i managed to make it work by adding MS DAO 3.6 Object Library.
but the thing i wanted to do is to search for a record, e.g. by typing ref number in textbox and i get other textboxes populated with the info from database when find button is pressed. "Haroon" wrote: hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
You need to add a reference to the Microsoft DAO 3.6 Object Library
-- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
As mentioned before, set the MatchEntry attribute of the combobox to 1 -
fmMatchEntryComplete and the bound column of the combo box to the field that contains the number, with the column widths of any columns before it set to 0. Then when you type the number into the combobox, it will select the record that contains the number. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... i managed to make it work by adding MS DAO 3.6 Object Library. but the thing i wanted to do is to search for a record, e.g. by typing ref number in textbox and i get other textboxes populated with the info from database when find button is pressed. "Haroon" wrote: hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#11
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
thanks alot Doug, your a star *
managed to make it work, but i have not solved all the problem yet, once i select the currect ref number from the dropdown/combobox, i want other 2/3 textboxes to get populated with the info, e.g. cust name, cust add etc. is there a way of doing that? instead of getting all the data in 1 combobox, i want column data to appear in textboxes. cheers again. "Doug Robbins - Word MVP" wrote: You need to add a reference to the Microsoft DAO 3.6 Object Library -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#12
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
i used this code to fill textbox1 but i get error message
textbox1 = combobox1.column(2) error: run-time error '381' could not get the column property. invalid property array index. "Haroon" wrote: thanks alot Doug, your a star * managed to make it work, but i have not solved all the problem yet, once i select the currect ref number from the dropdown/combobox, i want other 2/3 textboxes to get populated with the info, e.g. cust name, cust add etc. is there a way of doing that? instead of getting all the data in 1 combobox, i want column data to appear in textboxes. cheers again. "Doug Robbins - Word MVP" wrote: You need to add a reference to the Microsoft DAO 3.6 Object Library -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#13
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
To access the information in the other columns of the combobox, use code to
set the .BoundColumn attribute of the combo box. With combobox1 .BoundColumn = 2 Textbox1.Text = Value .BoundColumn = 3 .TextBox2.Text = .Value End With I suggest that you give meaningful names to each of the controls on your form -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks alot Doug, your a star * managed to make it work, but i have not solved all the problem yet, once i select the currect ref number from the dropdown/combobox, i want other 2/3 textboxes to get populated with the info, e.g. cust name, cust add etc. is there a way of doing that? instead of getting all the data in 1 combobox, i want column data to appear in textboxes. cheers again. "Doug Robbins - Word MVP" wrote: You need to add a reference to the Microsoft DAO 3.6 Object Library -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#14
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
Hi Doug,
I added the code but its not displaying any values in the textboxes. ComboBox1.Column = rs.GetRows(NoOfRecords) With ComboBox1 .BoundColumn = 1 TextBox1.Value = Value .BoundColumn = 2 TextBox2.Value = Value End With ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing do i put the code somewhere else? "Doug Robbins - Word MVP" wrote: To access the information in the other columns of the combobox, use code to set the .BoundColumn attribute of the combo box. With combobox1 .BoundColumn = 2 Textbox1.Text = Value .BoundColumn = 3 .TextBox2.Text = .Value End With I suggest that you give meaningful names to each of the controls on your form -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks alot Doug, your a star * managed to make it work, but i have not solved all the problem yet, once i select the currect ref number from the dropdown/combobox, i want other 2/3 textboxes to get populated with the info, e.g. cust name, cust add etc. is there a way of doing that? instead of getting all the data in 1 combobox, i want column data to appear in textboxes. cheers again. "Doug Robbins - Word MVP" wrote: You need to add a reference to the Microsoft DAO 3.6 Object Library -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#15
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
changed:
TextBox1.Text = Value .BoundColumn = 2 TextBox2.Text = Value no success. "Haroon" wrote: Hi Doug, I added the code but its not displaying any values in the textboxes. ComboBox1.Column = rs.GetRows(NoOfRecords) With ComboBox1 .BoundColumn = 1 TextBox1.Value = Value .BoundColumn = 2 TextBox2.Value = Value End With ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing do i put the code somewhere else? "Doug Robbins - Word MVP" wrote: To access the information in the other columns of the combobox, use code to set the .BoundColumn attribute of the combo box. With combobox1 .BoundColumn = 2 Textbox1.Text = Value .BoundColumn = 3 .TextBox2.Text = .Value End With I suggest that you give meaningful names to each of the controls on your form -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks alot Doug, your a star * managed to make it work, but i have not solved all the problem yet, once i select the currect ref number from the dropdown/combobox, i want other 2/3 textboxes to get populated with the info, e.g. cust name, cust add etc. is there a way of doing that? instead of getting all the data in 1 combobox, i want column data to appear in textboxes. cheers again. "Doug Robbins - Word MVP" wrote: You need to add a reference to the Microsoft DAO 3.6 Object Library -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#16
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
solved!!!
Private Sub ComboBox1_Click() TextBox1.Value = ComboBox1.Column(2) TextBox2.Value = ComboBox1.Column(1) thanks. "Haroon" wrote: changed: TextBox1.Text = Value .BoundColumn = 2 TextBox2.Text = Value no success. "Haroon" wrote: Hi Doug, I added the code but its not displaying any values in the textboxes. ComboBox1.Column = rs.GetRows(NoOfRecords) With ComboBox1 .BoundColumn = 1 TextBox1.Value = Value .BoundColumn = 2 TextBox2.Value = Value End With ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing do i put the code somewhere else? "Doug Robbins - Word MVP" wrote: To access the information in the other columns of the combobox, use code to set the .BoundColumn attribute of the combo box. With combobox1 .BoundColumn = 2 Textbox1.Text = Value .BoundColumn = 3 .TextBox2.Text = .Value End With I suggest that you give meaningful names to each of the controls on your form -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks alot Doug, your a star * managed to make it work, but i have not solved all the problem yet, once i select the currect ref number from the dropdown/combobox, i want other 2/3 textboxes to get populated with the info, e.g. cust name, cust add etc. is there a way of doing that? instead of getting all the data in 1 combobox, i want column data to appear in textboxes. cheers again. "Doug Robbins - Word MVP" wrote: You need to add a reference to the Microsoft DAO 3.6 Object Library -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#17
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
Why can't i import data from tables which have space in the table name?
e.g. cust details, vb gives error ''Run-time error '3131': Syntax error in FROM clause.'' is there a work around this? cheers. "Haroon" wrote: solved!!! Private Sub ComboBox1_Click() TextBox1.Value = ComboBox1.Column(2) TextBox2.Value = ComboBox1.Column(1) thanks. "Haroon" wrote: changed: TextBox1.Text = Value .BoundColumn = 2 TextBox2.Text = Value no success. "Haroon" wrote: Hi Doug, I added the code but its not displaying any values in the textboxes. ComboBox1.Column = rs.GetRows(NoOfRecords) With ComboBox1 .BoundColumn = 1 TextBox1.Value = Value .BoundColumn = 2 TextBox2.Value = Value End With ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing do i put the code somewhere else? "Doug Robbins - Word MVP" wrote: To access the information in the other columns of the combobox, use code to set the .BoundColumn attribute of the combo box. With combobox1 .BoundColumn = 2 Textbox1.Text = Value .BoundColumn = 3 .TextBox2.Text = .Value End With I suggest that you give meaningful names to each of the controls on your form -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks alot Doug, your a star * managed to make it work, but i have not solved all the problem yet, once i select the currect ref number from the dropdown/combobox, i want other 2/3 textboxes to get populated with the info, e.g. cust name, cust add etc. is there a way of doing that? instead of getting all the data in 1 combobox, i want column data to appear in textboxes. cheers again. "Doug Robbins - Word MVP" wrote: You need to add a reference to the Microsoft DAO 3.6 Object Library -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#18
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
I think the example in this conversation was
SELECT * FROM Owners If you had a table called "Car Owners", you need SELECT * FROM [Car Owners] (there are other ways to do it, but I think this is the clearest) Peter Jamieson http://tips.pjmsn.me.uk Haroon wrote: Why can't i import data from tables which have space in the table name? e.g. cust details, vb gives error ''Run-time error '3131': Syntax error in FROM clause.'' is there a work around this? cheers. "Haroon" wrote: solved!!! Private Sub ComboBox1_Click() TextBox1.Value = ComboBox1.Column(2) TextBox2.Value = ComboBox1.Column(1) thanks. "Haroon" wrote: changed: TextBox1.Text = Value .BoundColumn = 2 TextBox2.Text = Value no success. "Haroon" wrote: Hi Doug, I added the code but its not displaying any values in the textboxes. ComboBox1.Column = rs.GetRows(NoOfRecords) With ComboBox1 .BoundColumn = 1 TextBox1.Value = Value .BoundColumn = 2 TextBox2.Value = Value End With ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing do i put the code somewhere else? "Doug Robbins - Word MVP" wrote: To access the information in the other columns of the combobox, use code to set the .BoundColumn attribute of the combo box. With combobox1 .BoundColumn = 2 Textbox1.Text = Value .BoundColumn = 3 .TextBox2.Text = .Value End With I suggest that you give meaningful names to each of the controls on your form -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks alot Doug, your a star * managed to make it work, but i have not solved all the problem yet, once i select the currect ref number from the dropdown/combobox, i want other 2/3 textboxes to get populated with the info, e.g. cust name, cust add etc. is there a way of doing that? instead of getting all the data in 1 combobox, i want column data to appear in textboxes. cheers again. "Doug Robbins - Word MVP" wrote: You need to add a reference to the Microsoft DAO 3.6 Object Library -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#19
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
In what Event did you put the code? It has to be triggered by something
appropriate. Most probably the Combobox Exit event would be the best, though the Afterupdate or Change might do. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... Hi Doug, I added the code but its not displaying any values in the textboxes. ComboBox1.Column = rs.GetRows(NoOfRecords) With ComboBox1 .BoundColumn = 1 TextBox1.Value = Value .BoundColumn = 2 TextBox2.Value = Value End With ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing do i put the code somewhere else? "Doug Robbins - Word MVP" wrote: To access the information in the other columns of the combobox, use code to set the .BoundColumn attribute of the combo box. With combobox1 .BoundColumn = 2 Textbox1.Text = Value .BoundColumn = 3 .TextBox2.Text = .Value End With I suggest that you give meaningful names to each of the controls on your form -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks alot Doug, your a star * managed to make it work, but i have not solved all the problem yet, once i select the currect ref number from the dropdown/combobox, i want other 2/3 textboxes to get populated with the info, e.g. cust name, cust add etc. is there a way of doing that? instead of getting all the data in 1 combobox, i want column data to appear in textboxes. cheers again. "Doug Robbins - Word MVP" wrote: You need to add a reference to the Microsoft DAO 3.6 Object Library -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#20
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
i put the code in
Private Sub Combobox1_click() textbox1.value = combobox1.Column(1) '' i one of my database, i have 16100 records, it is not doing auto find/match when i type the ref number, e.g. 16100, but it does when i do it in database with 5 records. any ideas? "Doug Robbins - Word MVP" wrote: In what Event did you put the code? It has to be triggered by something appropriate. Most probably the Combobox Exit event would be the best, though the Afterupdate or Change might do. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... Hi Doug, I added the code but its not displaying any values in the textboxes. ComboBox1.Column = rs.GetRows(NoOfRecords) With ComboBox1 .BoundColumn = 1 TextBox1.Value = Value .BoundColumn = 2 TextBox2.Value = Value End With ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing do i put the code somewhere else? "Doug Robbins - Word MVP" wrote: To access the information in the other columns of the combobox, use code to set the .BoundColumn attribute of the combo box. With combobox1 .BoundColumn = 2 Textbox1.Text = Value .BoundColumn = 3 .TextBox2.Text = .Value End With I suggest that you give meaningful names to each of the controls on your form -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks alot Doug, your a star * managed to make it work, but i have not solved all the problem yet, once i select the currect ref number from the dropdown/combobox, i want other 2/3 textboxes to get populated with the info, e.g. cust name, cust add etc. is there a way of doing that? instead of getting all the data in 1 combobox, i want column data to appear in textboxes. cheers again. "Doug Robbins - Word MVP" wrote: You need to add a reference to the Microsoft DAO 3.6 Object Library -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#21
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
thanks Peter, ur a star *
"Peter Jamieson" wrote: I think the example in this conversation was SELECT * FROM Owners If you had a table called "Car Owners", you need SELECT * FROM [Car Owners] (there are other ways to do it, but I think this is the clearest) Peter Jamieson http://tips.pjmsn.me.uk Haroon wrote: Why can't i import data from tables which have space in the table name? e.g. cust details, vb gives error ''Run-time error '3131': Syntax error in FROM clause.'' is there a work around this? cheers. "Haroon" wrote: solved!!! Private Sub ComboBox1_Click() TextBox1.Value = ComboBox1.Column(2) TextBox2.Value = ComboBox1.Column(1) thanks. "Haroon" wrote: changed: TextBox1.Text = Value .BoundColumn = 2 TextBox2.Text = Value no success. "Haroon" wrote: Hi Doug, I added the code but its not displaying any values in the textboxes. ComboBox1.Column = rs.GetRows(NoOfRecords) With ComboBox1 .BoundColumn = 1 TextBox1.Value = Value .BoundColumn = 2 TextBox2.Value = Value End With ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing do i put the code somewhere else? "Doug Robbins - Word MVP" wrote: To access the information in the other columns of the combobox, use code to set the .BoundColumn attribute of the combo box. With combobox1 .BoundColumn = 2 Textbox1.Text = Value .BoundColumn = 3 .TextBox2.Text = .Value End With I suggest that you give meaningful names to each of the controls on your form -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks alot Doug, your a star * managed to make it work, but i have not solved all the problem yet, once i select the currect ref number from the dropdown/combobox, i want other 2/3 textboxes to get populated with the info, e.g. cust name, cust add etc. is there a way of doing that? instead of getting all the data in 1 combobox, i want column data to appear in textboxes. cheers again. "Doug Robbins - Word MVP" wrote: You need to add a reference to the Microsoft DAO 3.6 Object Library -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. |
#22
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
Loading 16,100 records into a combobox might be stretching things a bit and
my suggestion of using a combobox was make because it is a more certain way of ensuring that the information that the user supplies is actually in the source of the data. In the following article, there is another way of going about it that you should be able to use with some modifications "Access a database and insert into a Word document the data that you find there" at: http://www.word.mvps.org/FAQs/InterD...DataFromDB.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... i put the code in Private Sub Combobox1_click() textbox1.value = combobox1.Column(1) '' i one of my database, i have 16100 records, it is not doing auto find/match when i type the ref number, e.g. 16100, but it does when i do it in database with 5 records. any ideas? "Doug Robbins - Word MVP" wrote: In what Event did you put the code? It has to be triggered by something appropriate. Most probably the Combobox Exit event would be the best, though the Afterupdate or Change might do. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... Hi Doug, I added the code but its not displaying any values in the textboxes. ComboBox1.Column = rs.GetRows(NoOfRecords) With ComboBox1 .BoundColumn = 1 TextBox1.Value = Value .BoundColumn = 2 TextBox2.Value = Value End With ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing do i put the code somewhere else? "Doug Robbins - Word MVP" wrote: To access the information in the other columns of the combobox, use code to set the .BoundColumn attribute of the combo box. With combobox1 .BoundColumn = 2 Textbox1.Text = Value .BoundColumn = 3 .TextBox2.Text = .Value End With I suggest that you give meaningful names to each of the controls on your form -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks alot Doug, your a star * managed to make it work, but i have not solved all the problem yet, once i select the currect ref number from the dropdown/combobox, i want other 2/3 textboxes to get populated with the info, e.g. cust name, cust add etc. is there a way of doing that? instead of getting all the data in 1 combobox, i want column data to appear in textboxes. cheers again. "Doug Robbins - Word MVP" wrote: You need to add a reference to the Microsoft DAO 3.6 Object Library -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. i dont want to use the search option in mailmerge to find records, as i also want the user to type/add additional data on the textboxes in the form to be placed in bookmarks on document, as some data is not coming from database. anyone got idea? thanks in advance |
#23
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
Fortunately for me, Doug has been doing all the hard work :-)
Peter Jamieson http://tips.pjmsn.me.uk Haroon wrote: thanks Peter, ur a star * "Peter Jamieson" wrote: I think the example in this conversation was SELECT * FROM Owners If you had a table called "Car Owners", you need SELECT * FROM [Car Owners] (there are other ways to do it, but I think this is the clearest) Peter Jamieson http://tips.pjmsn.me.uk Haroon wrote: Why can't i import data from tables which have space in the table name? e.g. cust details, vb gives error ''Run-time error '3131': Syntax error in FROM clause.'' is there a work around this? cheers. "Haroon" wrote: solved!!! Private Sub ComboBox1_Click() TextBox1.Value = ComboBox1.Column(2) TextBox2.Value = ComboBox1.Column(1) thanks. "Haroon" wrote: changed: TextBox1.Text = Value .BoundColumn = 2 TextBox2.Text = Value no success. "Haroon" wrote: Hi Doug, I added the code but its not displaying any values in the textboxes. ComboBox1.Column = rs.GetRows(NoOfRecords) With ComboBox1 .BoundColumn = 1 TextBox1.Value = Value .BoundColumn = 2 TextBox2.Value = Value End With ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing do i put the code somewhere else? "Doug Robbins - Word MVP" wrote: To access the information in the other columns of the combobox, use code to set the .BoundColumn attribute of the combo box. With combobox1 .BoundColumn = 2 Textbox1.Text = Value .BoundColumn = 3 .TextBox2.Text = .Value End With I suggest that you give meaningful names to each of the controls on your form -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks alot Doug, your a star * managed to make it work, but i have not solved all the problem yet, once i select the currect ref number from the dropdown/combobox, i want other 2/3 textboxes to get populated with the info, e.g. cust name, cust add etc. is there a way of doing that? instead of getting all the data in 1 combobox, i want column data to appear in textboxes. cheers again. "Doug Robbins - Word MVP" wrote: You need to add a reference to the Microsoft DAO 3.6 Object Library -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. See the following pages of fellow MVP Greg Maxey's website: http://gregmaxey.mvps.org/Create_and...a_UserForm.htm http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi i made a form using ms word 2003 vba with few textboxes, when the user types in one of the field, e.g. customer ref, the database imports few selected field from databse into bookmarks on word document, e.g. customr name, add, date, etc. |
#24
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
thanks but i wanted to use the combobox on form so that the user can find the
ref, number instead of going through 16000 records to find it. "Doug Robbins - Word MVP" wrote: Loading 16,100 records into a combobox might be stretching things a bit and my suggestion of using a combobox was make because it is a more certain way of ensuring that the information that the user supplies is actually in the source of the data. In the following article, there is another way of going about it that you should be able to use with some modifications "Access a database and insert into a Word document the data that you find there" at: http://www.word.mvps.org/FAQs/InterD...DataFromDB.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... i put the code in Private Sub Combobox1_click() textbox1.value = combobox1.Column(1) '' i one of my database, i have 16100 records, it is not doing auto find/match when i type the ref number, e.g. 16100, but it does when i do it in database with 5 records. any ideas? "Doug Robbins - Word MVP" wrote: In what Event did you put the code? It has to be triggered by something appropriate. Most probably the Combobox Exit event would be the best, though the Afterupdate or Change might do. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... Hi Doug, I added the code but its not displaying any values in the textboxes. ComboBox1.Column = rs.GetRows(NoOfRecords) With ComboBox1 .BoundColumn = 1 TextBox1.Value = Value .BoundColumn = 2 TextBox2.Value = Value End With ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing do i put the code somewhere else? "Doug Robbins - Word MVP" wrote: To access the information in the other columns of the combobox, use code to set the .BoundColumn attribute of the combo box. With combobox1 .BoundColumn = 2 Textbox1.Text = Value .BoundColumn = 3 .TextBox2.Text = .Value End With I suggest that you give meaningful names to each of the controls on your form -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks alot Doug, your a star * managed to make it work, but i have not solved all the problem yet, once i select the currect ref number from the dropdown/combobox, i want other 2/3 textboxes to get populated with the info, e.g. cust name, cust add etc. is there a way of doing that? instead of getting all the data in 1 combobox, i want column data to appear in textboxes. cheers again. "Doug Robbins - Word MVP" wrote: You need to add a reference to the Microsoft DAO 3.6 Object Library -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that is populated with data from the Access database. |
#25
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
How to import certain fields from access database?
it auto fills/finds another colmn which i dont want to find, i want it to
filter/find column 1, also it only works if i write this sql select * from custs, but if i write select ref, custname, custadd from custs, it does not autofil combobox. any ideas? "Haroon" wrote: thanks but i wanted to use the combobox on form so that the user can find the ref, number instead of going through 16000 records to find it. "Doug Robbins - Word MVP" wrote: Loading 16,100 records into a combobox might be stretching things a bit and my suggestion of using a combobox was make because it is a more certain way of ensuring that the information that the user supplies is actually in the source of the data. In the following article, there is another way of going about it that you should be able to use with some modifications "Access a database and insert into a Word document the data that you find there" at: http://www.word.mvps.org/FAQs/InterD...DataFromDB.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... i put the code in Private Sub Combobox1_click() textbox1.value = combobox1.Column(1) '' i one of my database, i have 16100 records, it is not doing auto find/match when i type the ref number, e.g. 16100, but it does when i do it in database with 5 records. any ideas? "Doug Robbins - Word MVP" wrote: In what Event did you put the code? It has to be triggered by something appropriate. Most probably the Combobox Exit event would be the best, though the Afterupdate or Change might do. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... Hi Doug, I added the code but its not displaying any values in the textboxes. ComboBox1.Column = rs.GetRows(NoOfRecords) With ComboBox1 .BoundColumn = 1 TextBox1.Value = Value .BoundColumn = 2 TextBox2.Value = Value End With ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing do i put the code somewhere else? "Doug Robbins - Word MVP" wrote: To access the information in the other columns of the combobox, use code to set the .BoundColumn attribute of the combo box. With combobox1 .BoundColumn = 2 Textbox1.Text = Value .BoundColumn = 3 .TextBox2.Text = .Value End With I suggest that you give meaningful names to each of the controls on your form -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks alot Doug, your a star * managed to make it work, but i have not solved all the problem yet, once i select the currect ref number from the dropdown/combobox, i want other 2/3 textboxes to get populated with the info, e.g. cust name, cust add etc. is there a way of doing that? instead of getting all the data in 1 combobox, i want column data to appear in textboxes. cheers again. "Doug Robbins - Word MVP" wrote: You need to add a reference to the Microsoft DAO 3.6 Object Library -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for the code but when i run the 2nd code, i get error: ---------------------------------- compile error: user-defined type not defined ----------------------------------- as dao.database is highlighted i also added MS ActiveX data objects recordset 2.7 library and MS ActiveX data objects 2.5 Library, but still not working. "Doug Robbins - Word MVP" wrote: You should look at the links in my initial response to this thread. Here are a couple of routines. The first imports data from a database into a Word document and the second populates a listbox (or it could be a combobox) on a userform with data from a table in an Access database Dim myDataBase As Database Dim myActiveRecord As Recordset Dim i As Long Dim dtable As Table, drow As Row 'Open a database Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb") 'Access the first record from a particular table Set myActiveRecord = myDataBase.OpenRecordset("Currencies", dbOpenForwardOnly) 'Add a table to the document with one row and as many fields as there are in the database table Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, numcolumns:=myActiveRecord.Fields.Count) Set drow = dtable.Rows(1) 'Loop through all the records in the table until the end-of-file marker is reached Do While Not myActiveRecord.EOF 'Populate the cells in the Word table with the data from the current record For i = 1 To myActiveRecord.Fields.Count drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) Next i 'Add a new row to the Word table and access the next record Set drow = dtable.Rows.Add myActiveRecord.MoveNext Loop 'The last row will be empty, so delete it drow.Delete 'Then close the database myActiveRecord.Close myDataBase.Close Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("D:\Access\ResidencesXP.mdb") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM Owners") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset ListBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records ListBox1.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... thanks doug, what will be the code for importing fields into from/word from access? "Doug Robbins - Word MVP" wrote: Use a Combobox on the form that is populated with data from the database and then set the MatchEntry attribute of the combobox to 1 - fmMatchEntryComplete. Then if the use types the word into the combobox, the record that contains that word in that field will be selected. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Haroon" wrote in message ... hi Doug, thanks for reply, i want to add a search box on the form to search for data from the database, e.g. have a textbox where user types in cust. ref and presses search button and relevant textboxes get populated with the results, e.g. cust add, date, etc. "Doug Robbins - Word MVP" wrote: Use a userform that contains a combobox or list box that |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to link fields in Word with an Access database | Microsoft Word Help | |||
can't import fields in Excel having spaces to my Access table | Microsoft Word Help | |||
How do I get Mail Merge Recipients to import all my database? | Mailmerge | |||
Create 5x8 contact card with fields to import from Access. | Microsoft Word Help | |||
How to insert only selected fields from Access database in Word? | Microsoft Word Help |