[RESOLVED] Data Retrive in access

[nileshhthakur2004]

Hi all,

i have one Access on which i have ComboBox when i select the value in combobox it bind the othere control's in subform1 there is one LocationID TextBox on this subform1, now i want to featch (Retrive) value from depends on this LocationID and display it in SubForm2 on same form how can i do this ??

IS THIS POSIBLE TO RETRIVE THE VALUE FROM DATABASE BY WRITING THE USER DEFINE SELECT STATEMENT ???

LIKE
"SELECT * FROM SHIPTOLOCATION WHERE CUSTOMERID IS txtCustomer.Text" ???

pls Help me regarding this ??
thanx in advance

[KGComputers]

Hi,

can you attach the picture of this form/subform?

[KGComputers]

IS THIS POSIBLE TO RETRIVE THE VALUE FROM DATABASE BY WRITING THE USER DEFINE SELECT STATEMENT ???

LIKE
"SELECT * FROM SHIPTOLOCATION WHERE CUSTOMERID IS txtCustomer.Text" ???

Yes,it is possible...You may use the concept of DAO or ADO...Access supports ADO and DAO...Some programmers prefer DAO, others prefer ADO...

[nileshhthakur2004]

Hi KDComputer,

i have attached the image file .. pls check it

in this image it show me the customername "National Grocers" and CustomerID = 2
in subForm name OrderHeader i enter the appropriate CustomerID = 2 now base on this CustomerID 2 i want to retrive the value for OrderNbr i want to find the value using this Select statement

"select Max(OrderNbr) as MaxNumber from Orderheader where CustomerID = "2""

and place the MaxNumber in "OrderHeader" sub form's OrderNbr textBox Control ...

How can i do this ??

pls help me and also send some sample code or same DB ...

thanx and regards,

[nileshhthakur2004]

basicaly i want to Add the new Order For OrderHeader and OrderDetail fro the perticular CustomerID which use select from DropDown....
else sugest me any othere way...

thanx and regards,

[KGComputers]

Hi,

Have you tried viewing the northwinds database? Basically, your attached jpeg image reflects to Northwinds database....It's included in the visual studio package..Have you downloaded my simple search program in your previous thread regarding access forms?

[nileshhthakur2004]

hi KGComputer ,

my Db is not the Northwind and i have used you Sample DB,for search but my requirement is some thing diffrent ....

pls help me regarding this ...

Regards and thanx

[srisa]

I hope this helps . This is code using ado.
You have to add reference to Microsoft ActiveX Data Objects 2.5 library in
Project > References

dim conn as adodb.connection
dim rs as adodb.recordset
set conn = new adodb.connection
with conn
   .connectionstring  = "Provider  = Microsoft.Jet.OLEDB.4.0"; _
                            & "Data Source = 'YOur database name and path"
   .open
end with
 
set rs=new adodb.recordset
rs.open "select Max(OrderNbr) as MaxNumber from Orderheader where CustomerID = '2'", conn,adopendynaset,adopenreadonly,adcmdtext
yourformname.yourtextbox.text = rs("MaxNumber")

I am assuming that customer id is a text data type
I am new to ado myself. You might to make some changes to adopendynaset and adopenradonly.
Good luck.

[nileshhthakur2004]

hi srisa,

thank you for your help , i have tried your code but it won't work , it show me error on this line as below..

rs.open "select Max(OrderNbr) as MaxNumber from Orderheader where CustomerID = '2'", conn,adopendynaset,adopenreadonly,adcmdtext

the Error is
"
Run-time error '3709':
The connection cannot be used to perform this operation.It is either closed or invalid in this context."

pls help me if any one know how to do this ???

thanx and regards,

[srisa]

It would be easy if you could post the code. The exact connection string that you are using. By the way which version of access are you using?

[nileshhthakur2004]

hi srisa,

i am going to post my code here i am using Access 2000

and code is as follows

Private Sub OrderNbr_Change()

Dim conn As adodb.Connection
Dim rs As adodb.Recordset
Set conn = New adodb.Connection


With conn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security
Info=False;Data Source=C:\Documents and
Settings\Administrator\Desktop\Joriki 02202006\Joriki_new_03_13_06.mdb;"
conn.Open
End With

Set rs = New adodb.Recordset
rs.Open "select Max(OrderNbr) as MaxNumber from Orderheaders where CustomerID
= " & OrderNbr.Text & ", conn, adOpenStatic , adLockOptimistic"

MsgBox rs("MaxNumber")

End Sub

pls help me Regarding this ..

thanx and regards,

[srisa]

As far as I know , for ms-access 2000, jetoledb4.0 is used not 3.51
change this part to

With conn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security 
Info=False;Data Source=C:\Documents and 
Settings\Administrator\Desktop\Joriki 02202006\Joriki_new_03_13_06.mdb;"
conn.Open
End With

to this

With conn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                   & "Persist Security Info=False;" _
                   & "Data Source=C:\Documents and Settings\Administrator\Desktop\Joriki 02202006\Joriki_new_03_13_06.mdb;"
.Open
End With

And this

rs.Open "select Max(OrderNbr) as MaxNumber from Orderheaders where CustomerID 
= " & OrderNbr.Text & ", conn, adOpenStatic , adLockOptimistic"

to this

rs.Open "select Max(OrderNbr) as MaxNumber from Orderheaders where CustomerID = '" & OrderNbr.Text & "'" _
         , conn, adOpenStatic , adLockOptimistic

[nileshhthakur2004]

hi sirsa,

thank you very much my code is working now, but there is one problem as you can
see in connection string i gave the path of my DB as hard code
like "C:\Documents and Settings\Administrator\Desktop\Joriki.mdb"

when i place my DB on othere PC it show me error DB not found in VB we Can use
App.Path & "\UPSThermalLabels.mdb"

can we use any such a map path function in access also ??

so DB will Run at any PC and on any Location ??

Thanx and regards,

[Killazzz]

Hello,

There are several ways in working from different locations.

I don't think the app path will work here. I've been looking at this for another project I'm working on.

One way to work is to use a configuration file on the local pc that stores the path of the main db. This file can be read during startup and stored in a global variable.

Another way is to use linked tables in access. During start up you just need to check the link-path. If the path changes you get a popup asking you for the database location.

I've attached some code for refreshing the links.
J

[srisa]

Glad to know that I could be of some help to someone. That lets me know that I know something.
I don't know anything about accessing remote databases. So I cannot be of much help there.
Searching the forum might lead you to something.