Indirect referencing

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

H. Martins

Is there a way (VBA) to get the content of a field whose record number
is pointed by another field in another table?

Both tables are associated. Problem is: two of the fields in one of
the tables point to the same other table. I can't find a way to
distinguish second table records.

Thanks
H. Martins
 
Can you give an example of what you mean?

It's not uncommon for a record to have multiple foreign keys, each of which
point to a different record in a related table.
 
Can you give an example of what you mean?

It's not uncommon for a record to have multiple foreign keys, each of which
point to a different record in a related table.
Click to expand...


Some time ago I posted a message with details, but got no answer:
http://groups.google.com/group/micr...coding/browse_thread/thread/fdfb65bf35910dc6#

Anyway,

I have table A and B

TableA has two KeySlave fields pointing to TableB as:

Table a:
PlaceOfBirthKSlave
PlaceOfRegistrationKSlave

Table B
KMaster
stringPlace

In a report, how do I refer stringPlace concerning PlaceOfBirthKSlave
and the same to PlaceOfRegistrationKSlave?

In a ListBox it is easy because RowSource also refers the respective
KSlave field. But in a text box, how do I define I want (as example)
the stringPlace relative to the PlaceOfBirth not the
PlaceOfRegistration?

H. Martins
 
You can create a query that presents both pieces of information:

SELECT POB.stringPlace AS POB, POR.stringPlace AS POR
FROM (TableA LEFT JOIN TableB AS POB
ON TableA.PlaceOfBirthKSlave = POB.KMaster)
LEFT JOIN TableB AS POR
ON TableA.PlaceOfRegistrationLSlave = POR.KMaster;

You could also just use the DLookup function as the ControlSource for the
text boxes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Can you give an example of what you mean?

It's not uncommon for a record to have multiple foreign keys, each of
which
point to a different record in a related table.
Click to expand...


Some time ago I posted a message with details, but got no answer:
http://groups.google.com/group/micr...coding/browse_thread/thread/fdfb65bf35910dc6#

Anyway,

I have table A and B

TableA has two KeySlave fields pointing to TableB as:

Table a:
PlaceOfBirthKSlave
PlaceOfRegistrationKSlave

Table B
KMaster
stringPlace

In a report, how do I refer stringPlace concerning PlaceOfBirthKSlave
and the same to PlaceOfRegistrationKSlave?

In a ListBox it is easy because RowSource also refers the respective
KSlave field. But in a text box, how do I define I want (as example)
the stringPlace relative to the PlaceOfBirth not the
PlaceOfRegistration?

H. Martins
 
You could also just use the DLookup function as the ControlSource for the
text boxes.
Click to expand...

I will start trying DLookup.

Indeed I made a mistake referring the use of a text box. In fact I
should have mentioned a report.

I suppose (I did not try) it would be straightforward in a TextBox.

The problem is that I have to construct the sentences of a
certificate, so I use lots of '&' in order to make text make sense. I
suppose that I could use DLookup in a OnCurrent event, to set some
string variable according to the needs and latter invoke the string
variable in the report. The problem is that there is no OnCurrent
event in reports. I am not sure I can insert DLookup in some 'inline
fashion' in the middle of the '&'s I have to use to construct the
sentences.

.... I am not sure I am being clear enough.

H. Martins
 
Sorry, no, you're not being clear.

First of all, reports have text boxes on them, so I don't understand the
distinction you're trying to make.

Without seeing what you're trying to do with the "lots of '&'", it's pretty
hard to offer any suggestions.
 
Doug,

Thank you for your attention.

You are right. Indeed one of the text boxes of the report is:

="Certifica-se que " & [strNome] & ", natural " & [strD] & " " &
[strLocal] & ", nascido a " & Format([dateDataNascimento];"d\/m\/
yyyy") & ", nacionalidade " & [strNacionalidadeB] & ", portador " &
[strDescCertificados] & " nº " & [strDI] & " emitido a " &
Format([dateBI-data-TX];"d\/m\/yyyy") & " " & [strDescEmissao] & " de
" & [strBI-Local-TX] & ", concluiu o Curso de Formação Profissional
de"

It works as it is but now, [strLocal] must be replaced by a
DLookup(expr, domain, [criteria]) and [strBI-Local-TX] by another
DLookup.

The problem is that in my language we say (sort of) I live 'in' New
York, I live 'on' Wasington. Things become complicated because of this
details. Also, city names may be masculine and feminine.

Thanks
H. Martins
 
So what happens when you replace [strLocal] and [strBI-Local-TX] with
DLookups?

Personally, I think you'd be far better off adding the lookups in the
underlying query to which the report is bound.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,

Thank you for your attention.

You are right. Indeed one of the text boxes of the report is:

="Certifica-se que " & [strNome] & ", natural " & [strD] & " " &
[strLocal] & ", nascido a " & Format([dateDataNascimento];"d\/m\/
yyyy") & ", nacionalidade " & [strNacionalidadeB] & ", portador " &
[strDescCertificados] & " nº " & [strDI] & " emitido a " &
Format([dateBI-data-TX];"d\/m\/yyyy") & " " & [strDescEmissao] & " de
" & [strBI-Local-TX] & ", concluiu o Curso de Formação Profissional
de"

It works as it is but now, [strLocal] must be replaced by a
DLookup(expr, domain, [criteria]) and [strBI-Local-TX] by another
DLookup.

The problem is that in my language we say (sort of) I live 'in' New
York, I live 'on' Wasington. Things become complicated because of this
details. Also, city names may be masculine and feminine.

Thanks
H. Martins
 
H. Martins said:
I will start trying DLookup.

Indeed I made a mistake referring the use of a text box. In fact I
should have mentioned a report.

I suppose (I did not try) it would be straightforward in a TextBox.

The problem is that I have to construct the sentences of a
certificate, so I use lots of '&' in order to make text make sense. I
suppose that I could use DLookup in a OnCurrent event, to set some
string variable according to the needs and latter invoke the string
variable in the report. The problem is that there is no OnCurrent
event in reports. I am not sure I can insert DLookup in some 'inline
fashion' in the middle of the '&'s I have to use to construct the
sentences.

... I am not sure I am being clear enough.
Click to expand...


Construct your sentence in the query that the report is based on.

exp1:"This is to certify that "& [YourNameGoesHere] & " is a really nice
guy."

If it's more complex you can use a function to do this.

If you need the name or whatever in a different case you will have problems
making it neat if names are involved. Fixed length is easy.
Exporting to Word will solve those problems.
I believe FMS has some utilities that will allow formatting in rich text,
and Access 2007 can also do this.
 
Ok. I got DLookup running fine.

Thanks.

Can I have some help on how to add lookups in the query itself?

Thanks
H. Martins
 
I'd suggested

SELECT POB.stringPlace AS POB, POR.stringPlace AS POR
FROM (TableA LEFT JOIN TableB AS POB
ON TableA.PlaceOfBirthKSlave = POB.KMaster)
LEFT JOIN TableB AS POR
ON TableA.PlaceOfRegistrationLSlave = POR.KMaster;

Does that not work for you?
 
I'd suggested

SELECT POB.stringPlace AS POB, POR.stringPlace AS POR
FROM (TableA LEFT JOIN TableB AS POB
ON TableA.PlaceOfBirthKSlave = POB.KMaster)
LEFT JOIN TableB AS POR
ON TableA.PlaceOfRegistrationLSlave = POR.KMaster;

Does that not work for you?
Click to expand...

Douglas,

I saved that possibility for later test in as a simple query as it
seems. I mean, the present query is much larger and I was afraid to
drown myself merging it with your proposal.

Anyway, looks like you are creating a sort of alias "POB.stringPlace
AS POB" and later assign it to a JOIN (I still do not understand the
use of parenthesis).

Having in mind that SQL code is still (at list a little bit) out of
may hand, and after checking that DLookup worked fine, I decided the
proposal in my memo for latter investigation.

I came back to this post because another expresion was absolutely
misterious for me:

exp1:"This is to certify that "& [YourNameGoesHere] & " is a really
nice
guy."

Where is the 'entrance door' for this expression in a query?

Thanks
H. Martins
 
Since I didn't post that code, I really have no idea what was meant by that.

One possibility is that [YourNameGoesHere] will be a parameter for which
you'll be prompted to supply a value. I suspect that isn't what you want.
Another possibility is that you're supposed to replace [YourNameGoesHere]
with the name of the appropriate field in your query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'd suggested

SELECT POB.stringPlace AS POB, POR.stringPlace AS POR
FROM (TableA LEFT JOIN TableB AS POB
ON TableA.PlaceOfBirthKSlave = POB.KMaster)
LEFT JOIN TableB AS POR
ON TableA.PlaceOfRegistrationLSlave = POR.KMaster;

Does that not work for you?
Click to expand...

Douglas,

I saved that possibility for later test in as a simple query as it
seems. I mean, the present query is much larger and I was afraid to
drown myself merging it with your proposal.

Anyway, looks like you are creating a sort of alias "POB.stringPlace
AS POB" and later assign it to a JOIN (I still do not understand the
use of parenthesis).

Having in mind that SQL code is still (at list a little bit) out of
may hand, and after checking that DLookup worked fine, I decided the
proposal in my memo for latter investigation.

I came back to this post because another expresion was absolutely
misterious for me:

exp1:"This is to certify that "& [YourNameGoesHere] & " is a really
nice
guy."

Where is the 'entrance door' for this expression in a query?

Thanks
H. Martins
 
H. Martins wrote:

The brackets could be left off, but this goes in your query where you would
normally place just a field name.
If you typer in just ":"This is to certify that "& [YourNameGoesHere] & "
is a really nice guy."
Access will place the "exp1" for you.
 
Back
Top