Need help with combobox

[sjslall]

I have 2 accdb files - one is the frontend form and the other the backend database

I have a combobox that needs to be populated with a field from the database. And after a new record is added, the combobox list needs to be refreshed/updated/requeryed with the new data that has been added to the table.

This is what i have already tried:

Dim db as dao.database
dim rs as dao.rs

set db=opendatabase("c:\project\db.accdb")
set rs=db.recordset("select distinct section from sectionTable", dbopensnapshot)
>>> the rs recordset now has 10 records

cbosection.recordsource=rs![section]

>>> when i click the dropdown there is just the 1st record

Please help with how to get the complete list populated

I have tried doing a "do while not rs.eof", which does populate the cbo, but unable to get a way to requery for new updated data.

Pls assist

 

[namliam]

You are populating the result instead of the query...

why not write yoru query into the rowsource for the combobox and simply requery the combobox?

 

[sjslall]

can u help with the query part.
I cannot hardcode the above distinct field query into the rowsource of the cbobox as the database is at another location.

so pls give an example of how to write the query to the rowsource.

should it be something like at runtime in vba:
cbo.rowsource="select distinct section from table"
or
cbo.rowsource=db.rs(cboquery)

pls assist

 

[namliam]

cbo.rowsource="select distinct section from table"

Should do...

 

[Dairy Farmer]

SELECT DISTINCT Table1.Field1 FROM Table1 ORDER BY Table1.Field1;

Where ever you enter the data make an AfterUpdate

Me![Combo1].Requery

 

[sjslall]

I have tried all the possible steps mentioned above.
Let me make it VERY CLEAR:
The table is on a database which is at a different location, so cbobox.rowsource is raising an error mentioning the datasource is on a different location.

I have tried assigning the cbobox.recordset to a recordset created with the database, but the cbo doesnot populate.

Rather to make it really simple, how do I setup a combobox to populate from a database which is at a different location from the frontend location

There are 2 accdb files:
accdb#1: front end has the combo box
accdb#2:backend has the table to populate the data

Need help with rowsourse or recordset

 

[Dairy Farmer]

The table is on a database which is at a different location...
...how do I setup a combobox to populate from a database which is at a different location from the frontend location

Silly question, but are your tables linked?

 

[sjslall]

NIce question:
What do we mean by "Table Linked" here.

Anyways how do we link the table

 

[sjslall]

I think we need to link the tables....

 

[Dairy Farmer]

From the Access help file

Link to data in another Access database
Linking lets you connect to data in another database without importing it, so that you can view and modify the latest data in both the source and destination databases without creating and maintaining two copies of the same data. You can link only to tables in another Access database. You cannot link to queries, forms, reports, macros, or modules.
When you link to a table in an Access database, Access creates a new table, called a linked table, which maintains a link to the source records and fields. Any changes you make to the data in the source database are reflected in the linked table in the destination database, and vice versa. However, you cannot change the structure of a linked table in the destination database. In other words, you cannot make changes to a linked table such as adding or deleting a field, or modifying the data type of a field.

 

[sjslall]

thanks on the update.
Found a code to run a refresh linked on all tables:

Relink Tables in Code

Ever had to relink tables as you've moved a database. Want users to map whatever drive they like. Let users store a database where they want and then ask for the location and pass it as a string to this sub and it will re-link all your linked tables
This bit of code can be pasted straight into a module and used from there.
'RelinkTables...Just as the name suggests pass a path to a database to this sub
'eg RelinkTables("c:\windows\test.mdb")
'and it will go through all the tables in your
'database and link them to the new location
'Written by John Hawkins 20/9/99 www.fabalou.com
Public Sub RelinkTables(NewPathname As String)
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs
'Loop through the tables collection
For Each Tdf In Tdfs
If Tdf.SourceTableName <> "" Then 'If the table source is other than a base table
Tdf.Connect = ";DATABASE=" & NewPathname 'Set the new source
Tdf.RefreshLink 'Refresh the link
End If
Next 'Goto next table
End Sub