Bidirectional Dlookup

  • Thread starter Thread starter H. Martins
  • Start date Start date
H

H. Martins

Is there a way to get a connection from database to a textbox
independent of the present form query?

Dlookup would be ok but it just retrieves data from database. I need
it both ways.

I use Access 2003.

Thanks
H. Martins
 
Is there a way to get a connection from database to a textbox
independent of the present form query?

Dlookup would be ok but it just retrieves data from database. I need
it both ways.

I use Access 2003.

Thanks
H. Martins
Click to expand...

Use a subform?

Or, populate field with code and, using event AfterUpdate write a data
into required table.

Regards,
Branislav Mihaljev
Microsoft Access MVP
 
Indeed, dlookups fills the TextBox, but I can't edit it so there is no
way to save the data even if I write an AfterUpdate event code to
store (something like Dim rs As DAO.Recordset)

I suppose I will have to do it all under DAO.Recordset. Right?

HM.

..
 
H. Martins said:
Indeed, dlookups fills the TextBox, but I can't edit it so there is no
way to save the data even if I write an AfterUpdate event code to
store (something like Dim rs As DAO.Recordset)

I suppose I will have to do it all under DAO.Recordset. Right?
Click to expand...
If ou can get the information you ahve all that is needed to write it back
with a simple query.
Get it, fill a text box me!soemthing = dLookup(...), then write it back in
some after event.
 
Is there a way to get a connection from database to a textbox
independent of the present form query?

Dlookup would be ok but it just retrieves data from database. I need
it both ways.

I use Access 2003.

Thanks
H. Martins
Click to expand...

I'd suggest a subform with *no* Master/Child link. What are the two tables
involved? What information do they contain? Is there any relationship between
the entities represented by the tables? Do you really want a main form based
on one table, with a subform based on another table, with neither having any
connection to the other?
 
I tried that, but the subform only becomes active (?) after the
mainform has it's own query established.

HM.
Click to expand...

What I have on mind in second case is to populate Unbound field using
code and some event. There are many events that you can use depending
on your form design, i.e. using event OnCurrent (when record change),
you can read data from second table and write it to Unbound field
using VBA:

Me.fldUBoundField = DLookup("[fldFieldName]", "tblTableName",
"[IDField] = " & Something)

You will got a value in Unbound field which you can change (retype) as
now DLookup is in the code. When you do that, use AfterUpdate event of
that field to write data back to second table using either SQL or
recordset or even query if that is easier for you to create.

Regards,
Branislav Mihaljev
Microsoft Access MVP
 
Branislav ,
Me.fldUBoundField = DLookup("[fldFieldName]", "tblTableName",
"[IDField] = " & Something)

You will got a value in Unbound field which you can change (retype) as now DLookup is in the code.
Click to expand...

I did that before, directly in TextBox.ControlSource but then I
couldn't retype the data. You say that if I use an event to populate
the TextBox (under VBA) it would allow me to edit the TextBox
(retype)?
When you do that, use AfterUpdate event of that field to write data back to second table using either SQL or recordset or even query if that is easier for you to create.
Click to expand...

I am interested in the query details. You mean, I can establish a
query (TextBox.ControlSource = QueryName) after having modified the
TextBox? Establising the Query after having changes the TextBox
content wouldn't revert the TextBox content to the original register/
field data?

As I said, I tried once again the SubForm and it worked. It was quite
messy to solve the graphics/layout details because the SubForm should
simulate a simple TextBox. I had to disable many bound graphics
elements. Even so, it was difficult to adjust graphic placement.

Thanks.
H. Martins
 
Branislav ,
Me.fldUBoundField = DLookup("[fldFieldName]", "tblTableName",
"[IDField] = " & Something)
Click to expand...
You will got a value in Unbound field which you can change (retype) as now DLookup is in the code.
Click to expand...

I did that before, directly in TextBox.ControlSource but then I
couldn't retype the data. You say that if I use an event to populate
the TextBox (under VBA) it would allow me to edit the TextBox
(retype)?
When you do that, use AfterUpdate event of that field to write data backto second table using either SQL or recordset or even query if that is easier for you to create.
Click to expand...

I am interested in the query details. You mean, I can establish a
query (TextBox.ControlSource = QueryName) after having modified the
TextBox? Establising the Query after having changes the TextBox
content wouldn't revert the TextBox content to the original register/
field data?

As I said, I tried once again the SubForm and it worked. It was quite
messy to solve the graphics/layout details because the SubForm should
simulate a simple TextBox. I had to disable many bound graphics
elements. Even so, it was difficult to adjust graphic placement.

Thanks.
H. Martins
Click to expand...

Hi,

I will give you a sample how it may work, and you can change it
depending on your form design.

You have a form and you want to see some field data from second table
in one text box which is somehow related to the current. Put a text
box on the form. Leave the Control Source of the text box empty. That
makes text box unbound - it does not have record source.

If your run the form you will see that field empty and you can type
anything into it. Content of the field will never auto update as it is
not related to table field nor it is value is controlled by VBA code.

OK, revert to design view. Select form event OnCurrent and create
[Event Procedure]. This will open VBA screen to write a code related
to record change. Write a code similar to this (change table and field
name to your 2nd table name and field from that table):

Me.fldUBoundField = DLookup("[fldFieldName]", "tblTableName", _
"[IDField] = " & Something)

fldUBoundField is the unbound text box name
[fldFieldName] is the field name of 2nd table
tblTableName is the the name of 2nd table
[IDField] is the ID field of 2nd table
"Something" is usually ID field of form record source table
* Above command will read the value of [fldFieldName] from 2nd table
named "tblTableName", where ID field of that table is the same as ID
of form record source table

Finally, you will have the code similar to this:

Private Sub Form_Current()
Me.fldUBoundField = DLookup("[fldFieldName]", "tblTableName", _
"[IDField] = " & Something)
End Sub

Now, if you run the form and change record you will see that field
show data in unbound text box, but you can retype that value. Program
will not update value in second table, so you need code to update data
in second table. Back to design view. Click that field and select
event AfterUpdate. This will run the code when you update value:

Private Sub fldUBoundField_AfterUpdate()
On Error GoTo ErrHandler
Dim strSQL As String
strSQL = "UPDATE 2ndFieldName SET 2ndTableName.2ndFieldName = " _
& [Forms]![CurrentFormName]![fldUBoundField] _
& " WHERE 2ndTableName.ID = " _
& [Forms]![CurrentFormName]![ID]
CurrentDb.Execute strSQL, dbFailOnError
Exit Sub
ErrHandler:
MsgBox Err.Description
End Sub

When you update value of fldUBoundField it will run above code. By
lines it will do the following:
1 - if error arise goto "ErrHandler:"
2 - dim the variable strSQL (str means string)
3-6 - this is SQL command similar to query in SQL view
7 - run the SQL to update value in 2nd table. Raise error if the SQL
fails.
8 - Leave the code
9-10 - Create a message box with error description i.e. if the table
is locked for updating (like when someone else is editing same data
and the record is locked) warn the user that update has failed.

Also you can have a query to update 2nd table field. In that case code
will look like this:

Private Sub fldUBoundField_AfterUpdate()
DoCmd.OpenQuery "QueryName"
End Sub

---
Of course, if you use subform you don't need above code, but you will
need to control and filter record source of the subform to show
correct data regarding record you are viewing in main form.

To conclude: you will add unbound text box control on the form,
without anything in ControlSource, and you will control, using code,
which data you will see and also the code will take care to update
field value. Does it makes sense now?

Regards,
Branislav Mihaljev
Microsoft Access MVP
 
Dear Branislav

It makes perfectly sense.

I will try this method next new form. In the last one, I used the
subform whose query interrogate a function that returns the parameter
to be used in the query's criteria.

In other forms I used both methods you suggest, but never both in the
same textbox.

Thanks
H. Martins
 
Back
Top