MS Query and Dialog Box 
Author Message
 MS Query and Dialog Box

Does anyone know how to have a query box with say;

Item ID
Item Make
Item Model
Item Color

Setup so that a user can type in the Make/Model/Color or
if the user types in an item ID, when the user exits the ID field
it queries a database for the Make Model and Color and
populates the dialog box fields with this information?

Cheers

- Al



Mon, 14 Nov 2005 04:50:28 GMT  
 MS Query and Dialog Box
Hi Al

You can use a procedure such as the following to load the information from a
database into a multicolumn listbox of which you need only display the ID
field and you can then use the Change() event of the listbox to populate
text boxes with the balance of the information for that ID.

Private Sub UserForm_Activate()
'allocate memory for the database object as a whole and for the active
record
Dim myDataBase As Database
Dim myActiveRecord As Recordset
Dim i As Integer, j As Integer, m As Integer, n As Integer
'Open a database
Set myDataBase = OpenDatabase("E:\Access97\Ely\ResidencesXP.mdb")
'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("Owners", dbOpenForwardOnly)
'Get the number of fields in the table
j = myActiveRecord.Fields.Count
'Get the number of Records in the table
'Loop through all the records in the table until the end-of-file marker is
reached
i = 0
Do While Not myActiveRecord.EOF
    i = i + 1
    'access the next record
    myActiveRecord.MoveNext
Loop
myActiveRecord.Close
'Set the number of columns in the listbox
ListBox1.ColumnCount = j
' Define an array to be loaded with the data
Dim MyArray() As Variant
'Load data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 2
    Set myActiveRecord = myDataBase.OpenRecordset("Owners",
dbOpenForwardOnly)
    m = 0
    Do While Not myActiveRecord.EOF
        MyArray(m, n) = myActiveRecord.Fields(n + 1)
        m = m + 1
    myActiveRecord.MoveNext
    Loop
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
'Then close the database
myActiveRecord.Close
myDataBase.Close
End Sub

Here's a bit of code from something else that shows how to access the data
from each column of the listbox by setting the .BoundColumn value

Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
    ListBox1.BoundColumn = i
    Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee

Please respond to the newsgroups for the benefit of others who may be
interested.

Hope this helps
Doug Robbins - Word MVP


Quote:
> Does anyone know how to have a query box with say;

> Item ID
> Item Make
> Item Model
> Item Color

> Setup so that a user can type in the Make/Model/Color or
> if the user types in an item ID, when the user exits the ID field
> it queries a database for the Make Model and Color and
> populates the dialog box fields with this information?

> Cheers

> - Al



Mon, 14 Nov 2005 08:30:57 GMT  
 MS Query and Dialog Box
The database is HUGE! I dont think a list box would be a good idea.

A Formfield? would be fine where the user simply types in the ID number.
As soon as the user exits that ID Formfield the otehr fields are populated?

But when I have had a closer look at your code I will try to adapt it :-)

- Al



Quote:
> Hi Al

> You can use a procedure such as the following to load the information from
a
> database into a multicolumn listbox of which you need only display the ID
> field and you can then use the Change() event of the listbox to populate
> text boxes with the balance of the information for that ID.

> Private Sub UserForm_Activate()
> 'allocate memory for the database object as a whole and for the active
> record
> Dim myDataBase As Database
> Dim myActiveRecord As Recordset
> Dim i As Integer, j As Integer, m As Integer, n As Integer
> 'Open a database
> Set myDataBase = OpenDatabase("E:\Access97\Ely\ResidencesXP.mdb")
> 'Access the first record from a particular table
> Set myActiveRecord = myDataBase.OpenRecordset("Owners", dbOpenForwardOnly)
> 'Get the number of fields in the table
> j = myActiveRecord.Fields.Count
> 'Get the number of Records in the table
> 'Loop through all the records in the table until the end-of-file marker is
> reached
> i = 0
> Do While Not myActiveRecord.EOF
>     i = i + 1
>     'access the next record
>     myActiveRecord.MoveNext
> Loop
> myActiveRecord.Close
> 'Set the number of columns in the listbox
> ListBox1.ColumnCount = j
> ' Define an array to be loaded with the data
> Dim MyArray() As Variant
> 'Load data into MyArray
> ReDim MyArray(i, j)
> For n = 0 To j - 2
>     Set myActiveRecord = myDataBase.OpenRecordset("Owners",
> dbOpenForwardOnly)
>     m = 0
>     Do While Not myActiveRecord.EOF
>         MyArray(m, n) = myActiveRecord.Fields(n + 1)
>         m = m + 1
>     myActiveRecord.MoveNext
>     Loop
> Next n
> ' Load data into ListBox1
> ListBox1.List() = MyArray
> 'Then close the database
> myActiveRecord.Close
> myDataBase.Close
> End Sub

> Here's a bit of code from something else that shows how to access the data
> from each column of the listbox by setting the .BoundColumn value

> Dim i As Integer, Addressee As String
> Addressee = ""
> For i = 1 To ListBox1.ColumnCount
>     ListBox1.BoundColumn = i
>     Addressee = Addressee & ListBox1.Value & vbCr
> Next i
> ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee

> Please respond to the newsgroups for the benefit of others who may be
> interested.

> Hope this helps
> Doug Robbins - Word MVP


> > Does anyone know how to have a query box with say;

> > Item ID
> > Item Make
> > Item Model
> > Item Color

> > Setup so that a user can type in the Make/Model/Color or
> > if the user types in an item ID, when the user exits the ID field
> > it queries a database for the Make Model and Color and
> > populates the dialog box fields with this information?

> > Cheers

> > - Al



Mon, 14 Nov 2005 19:35:56 GMT  
 MS Query and Dialog Box
On Thu, 29 May 2003 23:35:56 +1200, "Al & Cay Grant"

Quote:

>The database is HUGE! I dont think a list box would be a good idea.

Maybe this gives you an idee

this is a snipet of my macro to update my pricelists.
Take care: it uses DAO therefore you have to set a reference to it

Sub Update()

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim doc As Word.Document
Dim tmp As Variant
Dim SQL As String
Dim itemID as Integer

ItemID= WhatIWantToFind

Set db = OpenDatabase("D:\my.mdb")
SQL = "SELECT Make, Model, Color FROM tPreisliste WHERE ID=" &ItemID
Set rs = db.OpenRecordset(SQL, dbOpenSnapshot, dbReadOnly)
rs.MoveLast
if rs.recordcount<>0
        Item Model=rs.model
        Item make=rs.make
        Item color=rs.color
else
        ' this itemID is not existing
endif

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

Hope it helps



Mon, 14 Nov 2005 21:18:14 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Dialog box displayed during query insert or query update

2. Select Directory dialog box like Comm Dialog Box

3. VB component dialog box does not show MS Masked Edit Control 6.0

4. MS Word Open dialog box

5. Different version of MS Common Dialog Box control

6. Button Text / MS Dialog Boxes

7. Using VB to popup the MS Username/Password dialog box

8. Show SQL query dialog box

9. dialog box with list box or combo box

10. Using MS Windows 3.1 common dialogs DLL from MS Access

11. Using MS Windows 3.1 common dialogs DLL from MS Access

12. Using MS Windows 3.1 common dialogs DLL from MS Access

 

 
Powered by phpBB® Forum Software