Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Displaying external database table on form. 3

Status
Not open for further replies.
Moss100

Moss100

Technical User
Aug 10, 2004
586
GB
Hi - I have written the code below to open a table called 2666, which is located in a different database called mini (located on my c drive)'

On clicking a button, I want the loaded form to display the data from the external database - I do not want the data to be changed - just to be available for viewing.

At present when I click the button with my code, nothing is happening (not even errors). Can someone help me out?

Many thanks Mark



Private Sub btn_LOAD_ACCOUNT_DblClick(Cancel As Integer)

Dim rs As Recordset
Dim db As DAO.Database

Set db = OpenDatabase("c:\mini.mdb", , ReadOnly, False)

On Error Resume Next

Set rs = db.OpenRecordset("select * FROM 2666")

If Err > 0 Then
MsgBox "Can not open Account", , "ABBICUS"

Set Me.Recordset = rs

Me![Txtdate].ControlSource = "date"
Me![TxtTYPE].ControlSource = "type"
Me![TxtAmountIN].ControlSource = "Amount In"
Me![TxtAmountOUT].ControlSource = "Amount Out"

End If

On Error GoTo 0

End Sub
 
Sort by date Sort by votes
At least 2 problems:
1. "On clicking a button", but you have your code in [tt][red]_Dbl[/red]Click[/tt] event.
2. Comment out [tt]On Error Resume Next[/tt] and step thru your code.



Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Upvote 0 Downvote
Thansk for your help - the double click is OK for me..

I have modified the code as below (basically I have removed the error stuff and if statement.

The form now fills with data correctly (but I would like error traps and the message if possible).

I was trying to get the code to pop up a message "Can not open Account" if the table does not exist in the external database.

Can you help me with my code to acheive this?

Many thanks Mark (As you will guess I am trying to learn code!)


Private Sub btn_LOAD_ACCOUNT_DblClick(Cancel As Integer)

Dim rs As Recordset
Dim db As DAO.Database

Set db = OpenDatabase("c:\mini.mdb", , ReadOnly, False)

'On Error Resume Next

Set rs = db.OpenRecordset("select * FROM 2666")

'If Err > 0 Then
'MsgBox "Can not open Account", , "ABBICUS"

Set Me.Recordset = rs

Me![Txtdate].ControlSource = "date"
Me![TxtTYPE].ControlSource = "type"
Me![TxtAmountIN].ControlSource = "Amount In"
Me![TxtAmountOUT].ControlSource = "Amount Out"

'End If

'On Error GoTo 0

End Sub
 
Upvote 0 Downvote
Again, Moss100, could you at least attempt to use TGML markup to make your post easier to read. Practically everyone who has ever attempted to assist you in the past has made the effort to use TGML to markup list, code, SQL, etc. It's very easy so please ask if you have any questions.

You didn't have an "Else" in your code. I would also recommend adding [] around field names that either contain spaces or are reserved words.
Try something like:

Code: 
Private Sub btn_LOAD_ACCOUNT_DblClick(Cancel As Integer)
    Dim rs As Recordset
    Dim db As DAO.Database
    Set db = OpenDatabase("c:\mini.mdb", , ReadOnly, False)
    On Error Resume Next
[COLOR=#4E9A06]    ' consider specifying the fields in your SQL statement[/color]
    Set rs = db.OpenRecordset("select [date], [type], [Amount In], [Amount Out] FROM 2666")
 
    If Err > 0 Then
        MsgBox "Can not open Account", , "ABBICUS"
      [highlight #FCE94F]Else[/highlight]
        Set Me.Recordset = rs
        Me![Txtdate].ControlSource = "[date]"
        Me![TxtTYPE].ControlSource = "[type]"
        Me![TxtAmountIN].ControlSource = "[Amount In]"
        Me![TxtAmountOUT].ControlSource = "[Amount Out]"
    End If
 
    On Error GoTo 0
 
End Sub

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Thank you very much - sorry I didn't realise what TGML was - I will ensure I use it in future.

The code works great :) The final thing i wish to do is to get the code to choose the table depending on the value of a text box on the form.

So instead of looking for table 2666 as above, it would use the value from the text box on the form called txtaccount

I have tried the following - but no joy

Code: 
, [type], [Amount In], [Amount Out] FROM me.[txtaccount]")]

Thank you Mark
 
Upvote 0 Downvote
Consider this:

Code: 
Dim strSQL As String

strSQL = "select [date], [type], [Amount In], [Amount Out] FROM "[blue] & txtAccount.Text
[/blue][green]
'Debug.Print strSQL[/green]

Set rs = db.OpenRecordset(strSQL)

This will work assuming all your tables have fields: [date], [type], [Amount In], [Amount Out]

But instead of allowing user to TYPE the name of the table - they would have to KNOW it - request tables names from your DB and display them in the drop-down list to choose from.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Upvote 0 Downvote
Anyway, I'd replace this:
txtAccount.Text
with this:
Me!txtAccount.Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
For clarification, the "[highlight #FCE94F]Text[/highlight]" property is available when the control has the focus. The "[highlight #FCE94F]Value[/highlight]" property doesn't require having focus. [highlight #FCE94F]Value[/highlight] is also the default property of bound controls so it can be omitted.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Thank you so much for your help.

The code below is now working great.

I am a little unsure of whether I am setting the recordset twice for the form in this code????

Also if possible I would like the records sorted by date in decending order. I tried adding 'ORDER BY date desc' to strSQL, but that seemed to get an error again. I would be greatful for help and comments on how I should improve or present the code better. Many thanks people.

Code: 
Private Sub BTN_LOAD_ACCOUNT_DblClick(Cancel As Integer)

Dim rs As Recordset
Dim db As DAO.Database
 
 ' the path of the database - I do not want users to edit data or for the 
 'connection to stay open to the external database  
 
Set db = OpenDatabase("c:\mini.mdb", , ReadOnly, False)
    
  On Error Resume Next
     
Dim strSQL As String

  ' I can not be certain all tables contain exactly the same fields so I am using select all
  strSQL = "Select * FROM " & Me.[txtaccount].Value
    
  Set rs = db.OpenRecordset(strSQL)
     
  If Err > 0 Then
     
     'message if requested table does not exist for some reason     
     MsgBox "Can not open Account", , "ABBICUS"
        
   Else
      'sets fields on the unbound account display form         

      Set Me.Recordset = rs
      Me![Txtdate].ControlSource = "[date]"
      Me![TxtTYPE].ControlSource = "[type]"
      Me![TxtAmountIN].ControlSource = "[Amount In]"
      Me![TxtAmountOUT].ControlSource = "[Amount Out]"
      
End If
 
    On Error GoTo 0

End Sub

 
Upvote 0 Downvote
If you "can not be certain all tables contain exactly the same fields" then how can you expect your code to set the control sources will always work?

Try:

Code: 
Private Sub BTN_LOAD_ACCOUNT_DblClick(Cancel As Integer)

Dim rs As Recordset
Dim db As DAO.Database
 
[COLOR=#4E9A06] ' the path of the database - I do not want users to edit data or for the 
 'connection to stay open to the external database[/color]   
Set db = OpenDatabase("c:\mini.mdb", , ReadOnly, False)
    
  On Error Resume Next
     
Dim strSQL As String

[COLOR=#4E9A06]  ' I can not be certain all tables contain exactly the same fields so I am using select all[/color]
  strSQL = "Select * FROM " & Me.[txtaccount].Value & " ORDER BY [Date] DESC"
    
  Set rs = db.OpenRecordset(strSQL)
     
  If Err > 0 Then
     
     [COLOR=#4E9A06]'message if requested table does not exist for some reason[/color]     
     MsgBox "Can not open Account", , "ABBICUS"
        
   Else
      [COLOR=#4E9A06]'sets fields on the unbound account display form[/color]         

      Set Me.Recordset = rs
      Me![Txtdate].ControlSource = "[date]"
      Me![TxtTYPE].ControlSource = "[type]"
      Me![TxtAmountIN].ControlSource = "[Amount In]"
      Me![TxtAmountOUT].ControlSource = "[Amount Out]"
      
End If
 
    On Error GoTo 0

End Sub

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote

Code: 
strSQL = "Select * FROM " & Me.[txtaccount].Value[blue]
strSQL = strSQL & " ORDER BY date desc"[/blue]

Debug.Print strSQL

"I can not be certain all tables contain exactly the same fields so I am using select all"

In this case, if you do not have [date], [type], [Amount In], [Amount Out] in tables users can point to, you will crash somewhere here:

Code: 
Me![Txtdate].ControlSource = [red]"[date]"[/red]
Me![TxtTYPE].ControlSource = [red]"[type]"[/red]
Me![TxtAmountIN].ControlSource = [red]"[Amount In]"[/red]
Me![TxtAmountOUT].ControlSource = [red]"[Amount Out]"[/red]

:-(

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Upvote 0 Downvote
Ok thankfully I have now determined that all tables have exactly the same structure - so that will thankfully prevent the code crashing as highlighted.

I have left the sql as SELECT * as I may wish to add further fields into the form. As it runs at present, it is almost instant in returning results - its doing exactly what I set out to achieve thanks you the kind persons here.

A BIG THANK YOU.

Here is my final code (unless there are other suggested tweaks) - I hope it may help others.

Mark

Code: 
Private Sub BTN_LOAD_ACCOUNT_DblClick(Cancel As Integer)

Dim rs As Recordset
Dim db As DAO.Database
    
 Set db = OpenDatabase("c:\mini.mdb", , ReadOnly, False)
    
  On Error Resume Next
     
Dim strSQL As String
    
  strSQL = "Select * FROM " & Me.[txtAccount].Value
  strSQL = strSQL & " ORDER BY date desc"
  
  Set rs = db.OpenRecordset(strSQL)
     
  If Err > 0 Then
     
     MsgBox "Can not open Account", , "ABBICUS"
        
   Else
      
      Set Me.Recordset = rs
      Me![Txtdate].ControlSource = "[date]"
      Me![TxtTYPE].ControlSource = "[type]"
      Me![TxtAmountIN].ControlSource = "[Amount In]"
      Me![TxtAmountOUT].ControlSource = "[Amount Out]"

End If
 
    On Error GoTo 0

End Sub
 
Upvote 0 Downvote
The only other possible issue would be if the table name had spaces. If so, use:
Code: 
  strSQL = "Select * FROM [highlight #FCE94F][[/highlight]" & Me.[txtAccount].Value
  strSQL = strSQL & "[highlight #FCE94F]][/highlight] ORDER BY date desc"

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Hello again...

I have noted when I run the code, it keeps its connection with the external database open until I close the form.

I would prefer it to just open the recordset and then drop the connection with the external database.

Is this possible?

Thank you Mark
 
Upvote 0 Downvote
rs.Close

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
1. Instead of text box [tt]txtAccount[/tt] (users have to know the names of the tables, and you will run into errors when user types ‘XYZ’ as table name), you could use a drop-down list of tables’ names to choose from. How to get the list of tables in your Access DB here

2. If you work with set number of tables, you can just create a (UNION) query with all tables that you work with and get the data from just one place. No text box.

3. If "all tables have exactly the same structure", then why many tables?
Could you create just one table and have additional field in it? It would be the info from your tables names.

Number 2 and 3 are pretty much the same.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Upvote 0 Downvote
Hello ....

1. Instead of text box txtAccount (users have to know the names of the tables, and you will run into errors when user types ‘XYZ’ as table name), you could use a drop-down list of tables’ names to choose from. How to get the list of tables in your Access DB here

The txtAccount textbox is already populated from another table - so the user does not have to remember the table name.


2. If you work with set number of tables, you can just create a (UNION) query with all tables that you work with and get the data from just one place. No text box.

Each table is an individual account - I understand that this is not ideal - but the database that I am linking to is a professionally written package - and oddly in its sector is the market leader with over 3000 uk users


3. If "all tables have exactly the same structure", then why many tables?
Could you create just one table and have additional field in it? It would be the info from your tables names.

As above - at present the external database I link to have over 3200 tables!

Thanks - Mark

Number 2 and 3 are pretty much the same.
 
Upvote 0 Downvote
Hello - to clarify - I am trying to stop the external database from having a lock on it after the recordet is set.

Is there a way to close the connection to the external database, but still retain the recordset for use in the form of the current database?

Thank you Mark
 
Upvote 0 Downvote
From further research it seems that if I connect using ADO I can get a disconnected recordset (I don't think you can achieve this with DAO). Can anyone tell me:

1. Can I use ADO & DAO connections in the same database without problems or do I have to choose to go with one or the other?
2. Will a disconnected record set stop the lock being applied to the external database once the recordset is in memory?

Many thanks Mark
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

V
  • Locked
  • Helpful tip
Capture Shift Key if Pressed on A form
Replies
3
Views
324
strongm
strongm
Moss100
  • Locked
  • Question
Transfer data from external table to identical local table - urgent please
2
Replies
27
Views
569
Andrzejek
Andrzejek
Zyborg
  • Locked
  • Question
Using a variable as reference to a form
Replies
5
Views
313
Zyborg
Zyborg
Moss100
  • Locked
  • Question
Make database appear as just a form
Replies
1
Views
180
dhookom
dhookom
Moss100
  • Locked
  • Question
Open New Access Database and Maximise the Application Window
Replies
0
Views
300
Moss100
Moss100

Part and Inventory Search

Sponsor

Back
Top