Me.Recordset for linked tables. 1

[barrientos101]

If a linked table is a RecordSource for a form, will Me.Recordset work? It seems not to for me. Would I have to convert the table name in the RecordSource property to a query on that table (like SELECT * from table) for it to work ?

I am proficient at both SQL and VBA, so any hint would be appreciated. Thanks.

 

[dhookom]

What is your objective? Are you attempting to change the Recordset of a form or clone the recordset or what?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[barrientos101]

The linked table is the RecordSource for the form, and some of the fields are bound to fields on the form, but others are not. When a record in the table has been updated, I want to use that information to update the unbound fields of that record as well.

I was using code like:

Me.Recordset.Edit
Me.Recordset!LangSymbol = ctlLanguage.Column(1)
Me.Recordset.Update

But it doesn't work now that I've split my database. (As you can see, my text control has more than one column. Column 2 is the bound one, which when entered will put "Korean" into the table, if that's what the user has chosen. But in another field of that record in the table, I wish to put it's symbol, 'K'.)

Actually, if you can show me a way to do this update without doing so programmatically, that would be even better. Thanks!

 

[dhookom]

First, I'm not sure why you think you need to store a value that can be looked up. If ctlLanguage is bound, you should be able to use its value to always lookup the symbol.

However, why not bind the symbol field and then use code in the after update of ctlLanguage like:

    Me.txtLangSymbol = ctlLanguage.Column(1)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[barrientos101]

You do make a good point, but the reason I am doing that is because I am using the table to update another table, and I need the LangSymbol field and *not* the Language field. I could do the update query looking this up, but it would be more complicated.

But actually, you have solved my problem. I think your line of code assumes that I have a (possibly hidden) txtLangSymbol field on my form bound to the LangSymbol field in the table. I guess that would work.

However, your line of code made me realize something else. Why do I need the Language field bound at all? Why not make the language name visible, but make the hidden ctlLanguage.column(1) bound to the LangSymbol field in the table. That way when the user selects the language, it will be the *symbol* that gets into the table. I don't need a language field in the table at all! I redesigned the form that way (and changed the "required field" properties in the table) and it works! Thanks a bunch.