Help pleaseee... >.<

[marco_pb via AccessMonster.com]

Hi all,
thank you in advance to read my problem and to try to solve it..

I am creating a formula, where some part of it takes data from another
formula/table within a condition..
example:

form A
department name
IT me
HR you

Form B
Department name salary address
IT (Automatically show 'me') 9999 somewhere

how can I do it?
what will be the vb code?
please.. i really need to do this..
P.S. the name of the column is only an example.. what i want to make is more
complicated than this, no offense but i think it will be easier for you to
understand when i give the easy example.. thank you again..

 

[Marshall Barton]

I am creating a formula, where some part of it takes data from another
formula/table within a condition..
example:

form A
department name
IT me
HR you

Form B
Department name salary address
IT (Automatically show 'me') 9999 somewhere

how can I do it?
what will be the vb code?

Typically, this is done by using a Join in formB's record
source query. Without knowing more about your table
relationship, I can't be sure, but maybe this example query
will provide an idea:

SELECT tblB.Department, tblA.[Name],
tblB.Salary, tblB.Address
FROM tblB INNER JOIN tblA
ON tblA.Department = tblB.Department

Lacking that kind of relationship, you can fall back on
using an inefficient expression in the name text box:
=DLookup("Name", "tblA", "Department='" & Department & "'")

 

[marco_pb via AccessMonster.com]

thank you,

but the problem is, I need to edit the form, If I do so (your query
suggestion), I can not edit my form is it?

And if I use Dlookup, where can I put it?

thank you again in advance

I am creating a formula, where some part of it takes data from another
formula/table within a condition..

[quoted text clipped - 11 lines]

how can I do it?
what will be the vb code?

Typically, this is done by using a Join in formB's record
source query. Without knowing more about your table
relationship, I can't be sure, but maybe this example query
will provide an idea:

SELECT tblB.Department, tblA.[Name],
tblB.Salary, tblB.Address
FROM tblB INNER JOIN tblA
ON tblA.Department = tblB.Department

Lacking that kind of relationship, you can fall back on
using an inefficient expression in the name text box:
=DLookup("Name", "tblA", "Department='" & Department & "'")

 

[Marshall Barton]

If one of the query fields is tableB's primary key, the the
tableB fields should be editable.

The DLookup expression would be used as a text box's
ControlSource.
--
Marsh
MVP [MS Access]

but the problem is, I need to edit the form, If I do so (your query
suggestion), I can not edit my form is it?

And if I use Dlookup, where can I put it?

I am creating a formula, where some part of it takes data from another
formula/table within a condition..

[quoted text clipped - 11 lines]

how can I do it?
what will be the vb code?

Typically, this is done by using a Join in formB's record
source query. Without knowing more about your table
relationship, I can't be sure, but maybe this example query
will provide an idea:

SELECT tblB.Department, tblA.[Name],
tblB.Salary, tblB.Address
FROM tblB INNER JOIN tblA
ON tblA.Department = tblB.Department

Lacking that kind of relationship, you can fall back on
using an inefficient expression in the name text box:
=DLookup("Name", "tblA", "Department='" & Department & "'")

 

[marco_pb via AccessMonster.com]

thanks for the reply..

I have tried to add all the primary key, but still i can not edit and/or add
the record..

Thank you in advance for your help..

If one of the query fields is tableB's primary key, the the
tableB fields should be editable.

The DLookup expression would be used as a text box's
ControlSource.

but the problem is, I need to edit the form, If I do so (your query
suggestion), I can not edit my form is it?

[quoted text clipped - 20 lines]

 

[Marshall Barton]

If you can not edit any field when you open the query
directly from the query design or database window, then I
don't know what it is about your data arrangement that might
be locking you out. If you can edit a field in the query's
datasheet, then the form has some setting that is preventing
updates.

Did you try using the DLookup?
--
Marsh
MVP [MS Access]

I have tried to add all the primary key, but still i can not edit and/or add
the record..

If one of the query fields is tableB's primary key, the the
tableB fields should be editable.

The DLookup expression would be used as a text box's
ControlSource.

but the problem is, I need to edit the form, If I do so (your query
suggestion), I can not edit my form is it?

[quoted text clipped - 20 lines]

 

[marco_pb via AccessMonster.com]

I want to work with the editable query, but since it is not possible, so i am
using the table as the form source, and i call one column from another table
using Dlookup but there is no result (nothing happen) - i use docmd.openform
and then call the column ii need then close that other form again, and the
result is that the command i write only call the top most value of another
form (not assigning name with the department)

Thank you anyway for helping me this far..

If you can not edit any field when you open the query
directly from the query design or database window, then I
don't know what it is about your data arrangement that might
be locking you out. If you can edit a field in the query's
datasheet, then the form has some setting that is preventing
updates.

Did you try using the DLookup?

I have tried to add all the primary key, but still i can not edit and/or add
the record..

[quoted text clipped - 4 lines]

The DLookup expression would be used as a text box's
ControlSource.
but the problem is, I need to edit the form, If I do so (your query
suggestion), I can not edit my form is it?
[quoted text clipped - 20 lines]
using an inefficient expression in the name text box:
=DLookup("Name", "tblA", "Department='" & Department & "'")

 

[Marshall Barton]

You are going off the track now. There is no need to open
another form just to get the value of a field in a table.

You are not giving me the right information to help with
this. I need to see the two tables, i.e. their names, the
names of the relevant fields in each table along with the
primary and foreign key fields.

Also, if you need help with a query or the DLookup, I need
to see a Copy/Paste of the query's SQL view and the DLookup
expression.
--
Marsh
MVP [MS Access]

I want to work with the editable query, but since it is not possible, so i am
using the table as the form source, and i call one column from another table
using Dlookup but there is no result (nothing happen) - i use docmd.openform
and then call the column ii need then close that other form again, and the
result is that the command i write only call the top most value of another
form (not assigning name with the department)

If you can not edit any field when you open the query
directly from the query design or database window, then I
don't know what it is about your data arrangement that might
be locking you out. If you can edit a field in the query's
datasheet, then the form has some setting that is preventing
updates.

Did you try using the DLookup?

I have tried to add all the primary key, but still i can not edit and/or add
the record..

[quoted text clipped - 4 lines]

The DLookup expression would be used as a text box's
ControlSource.

but the problem is, I need to edit the form, If I do so (your query
suggestion), I can not edit my form is it?
[quoted text clipped - 20 lines]

using an inefficient expression in the name text box:
=DLookup("Name", "tblA", "Department='" & Department & "'")

 

[marco_pb via AccessMonster.com]

Thank you for helping me this far..
It is not I dont want to provide you the table and so on, but it is too
complicated to put all here.. I am really sorry, no offence..

I have another solution as I read and searched on the internet. I put it in
different thread, but i can copy it to you:

Private Sub Abteilung_AfterUpdate()
Dim Answer As Integer
Dim SaveErr As Long
Dim Dbs As Database
Dim rstx As Recordset
Dim Result As String
Dim Ausbilder As String
Stop
On Error GoTo Error_Handling

Stop
On Error GoTo 0
Set Dbs = DBEngine.Workspaces(0).Databases(0)
'Set Dbs = OpenDatabase("W:\Azubis\Praktikum\Marcories\Database\
Praktikanten 2003.mdb")
Set rstx = Dbs.OpenRecordset("Abteilungen", dbOpenTable)
If rst.BOF = False Then
Ausbilder = rst!Ausbildungsbetreuer
Me!Ausbildungsbetreuer = Ausbilder
End If
Dbs.Close

'Me!Ausbildungsbetreuer.RowSource = " "
'Me!Ausbildungsbetreuer.RowSource = "SELECT Ausbildungsbetreuer FROM
Abteilungen WHERE AID='" & Me![abteilung] & "';"
'Me.Refresh
'Stop
'Ausbildungsbetreuer.ListIndex = 0
Exit Sub

Error_Handling:
SaveErr = Err
If Err = 3058 Then
Resume Next
Else
Answer = MsgBox(Error(SaveErr), vbCritical + vbRetryCancel, "Praktianten
DB")
If Answer = vbCancel Then
Resume Next
Else
Resume
End If
End If
End Sub

Private Sub Praktikum_Art_AfterUpdate()
Dim a As String

a = Forms![Praktikanten]!Praktikum_Art

If (a = "Schülerpraktikum") Then
Forms![Praktikanten]!Vergütung.Value = 0
Else
If (a = "Grundpraktikum") Then
Forms![Praktikanten]!Vergütung.Value = 300
Else
Forms![Praktikanten]!Vergütung.Value = 500
End If
End If
End Sub
Private Sub Zurück_Click()
On Error GoTo Err_Zurück_Click

DoCmd.Close

Exit_Zurück_Click:
Exit Sub

Err_Zurück_Click:
MsgBox Err.Description
Resume Exit_Zurück_Click

End Sub

THANK YOU IN ADVANCE

You are going off the track now. There is no need to open
another form just to get the value of a field in a table.

You are not giving me the right information to help with
this. I need to see the two tables, i.e. their names, the
names of the relevant fields in each table along with the
primary and foreign key fields.

Also, if you need help with a query or the DLookup, I need
to see a Copy/Paste of the query's SQL view and the DLookup
expression.

I want to work with the editable query, but since it is not possible, so i am
using the table as the form source, and i call one column from another table

[quoted text clipped - 23 lines]

 

[Marshall Barton]

I'm sorry, but I am completely lost now. In the code you
posted, the Abteilung_AfterUpdate procedure looks like its
essence is the long way around of using the DLookup we were
talking about before. Just placing the expression:
=DLookup("Ausbildungsbetreuer", "Abteilungen")
in a text box would do the same thing.

But that doesn't appear to do what I thought you were asking
for because it will always find the "first" record in the
table. I am not at al certain, but I think you probably
need to add a criteria to the DLookup for the person
identitfied in the current record, maybe something like:
=DLookup("Ausbildungsbetreuer", "Abteilungen", "[name]="""
& [Name & """")
--
Marsh
MVP [MS Access]

It is not I dont want to provide you the table and so on, but it is too
complicated to put all here.. I am really sorry, no offence..

I have another solution as I read and searched on the internet. I put it in
different thread, but i can copy it to you:

Private Sub Abteilung_AfterUpdate()
Dim Answer As Integer
Dim SaveErr As Long
Dim Dbs As Database
Dim rstx As Recordset
Dim Result As String
Dim Ausbilder As String
Stop
On Error GoTo Error_Handling

Stop
On Error GoTo 0
Set Dbs = DBEngine.Workspaces(0).Databases(0)
'Set Dbs = OpenDatabase("W:\Azubis\Praktikum\Marcories\Database\
Praktikanten 2003.mdb")
Set rstx = Dbs.OpenRecordset("Abteilungen", dbOpenTable)
If rst.BOF = False Then
Ausbilder = rst!Ausbildungsbetreuer
Me!Ausbildungsbetreuer = Ausbilder
End If
Dbs.Close

'Me!Ausbildungsbetreuer.RowSource = " "
'Me!Ausbildungsbetreuer.RowSource = "SELECT Ausbildungsbetreuer FROM
Abteilungen WHERE AID='" & Me![abteilung] & "';"
'Me.Refresh
'Stop
'Ausbildungsbetreuer.ListIndex = 0
Exit Sub

Error_Handling:
SaveErr = Err
If Err = 3058 Then
Resume Next
Else
Answer = MsgBox(Error(SaveErr), vbCritical + vbRetryCancel, "Praktianten
DB")
If Answer = vbCancel Then
Resume Next
Else
Resume
End If
End If
End Sub

Private Sub Praktikum_Art_AfterUpdate()
Dim a As String

a = Forms![Praktikanten]!Praktikum_Art

If (a = "Schülerpraktikum") Then
Forms![Praktikanten]!Vergütung.Value = 0
Else
If (a = "Grundpraktikum") Then
Forms![Praktikanten]!Vergütung.Value = 300
Else
Forms![Praktikanten]!Vergütung.Value = 500
End If
End If
End Sub
Private Sub Zurück_Click()
On Error GoTo Err_Zurück_Click

DoCmd.Close

Exit_Zurück_Click:
Exit Sub

Err_Zurück_Click:
MsgBox Err.Description
Resume Exit_Zurück_Click

End Sub

THANK YOU IN ADVANCE

You are going off the track now. There is no need to open
another form just to get the value of a field in a table.

You are not giving me the right information to help with
this. I need to see the two tables, i.e. their names, the
names of the relevant fields in each table along with the
primary and foreign key fields.

Also, if you need help with a query or the DLookup, I need
to see a Copy/Paste of the query's SQL view and the DLookup
expression.

I want to work with the editable query, but since it is not possible, so i am
using the table as the form source, and i call one column from another table

[quoted text clipped - 23 lines]

using an inefficient expression in the name text box:
=DLookup("Name", "tblA", "Department='" & Department & "'")

 

[marco_pb via AccessMonster.com]

I have tried in in the text box of ausbildungsbetreuer (standard word, before
update event, and on focus event)
but nothing waas happening. after I choose abteilung in a combo box, when i
focus on the ausbildungsbetreuer, the value was not showing
=DLookup("Ausbildungsbetreuer";"Abteilungen";"[AID]=""" & [Abteilung] & """")
thank you for your help.

I'm sorry, but I am completely lost now. In the code you
posted, the Abteilung_AfterUpdate procedure looks like its
essence is the long way around of using the DLookup we were
talking about before. Just placing the expression:
=DLookup("Ausbildungsbetreuer", "Abteilungen")
in a text box would do the same thing.

But that doesn't appear to do what I thought you were asking
for because it will always find the "first" record in the
table. I am not at al certain, but I think you probably
need to add a criteria to the DLookup for the person
identitfied in the current record, maybe something like:
=DLookup("Ausbildungsbetreuer", "Abteilungen", "[name]="""
& [Name & """")

It is not I dont want to provide you the table and so on, but it is too
complicated to put all here.. I am really sorry, no offence..

[quoted text clipped - 94 lines]

 

[Marshall Barton]

Now, at least the DLookup appears to be sensible, but are
you sure the AID field in the table has a matching value for
the BoundColumn in the Abteilung combo box. Remember that
the BoundColumn may not be the first visible column so you
might not be seeing the combo box's Value that you are
searching for.
--
Marsh
MVP [MS Access]

I have tried in in the text box of ausbildungsbetreuer (standard word, before
update event, and on focus event)
but nothing waas happening. after I choose abteilung in a combo box, when i
focus on the ausbildungsbetreuer, the value was not showing
=DLookup("Ausbildungsbetreuer";"Abteilungen";"[AID]=""" & [Abteilung] & """")
thank you for your help.

I'm sorry, but I am completely lost now. In the code you
posted, the Abteilung_AfterUpdate procedure looks like its
essence is the long way around of using the DLookup we were
talking about before. Just placing the expression:
=DLookup("Ausbildungsbetreuer", "Abteilungen")
in a text box would do the same thing.

But that doesn't appear to do what I thought you were asking
for because it will always find the "first" record in the
table. I am not at al certain, but I think you probably
need to add a criteria to the DLookup for the person
identitfied in the current record, maybe something like:
=DLookup("Ausbildungsbetreuer", "Abteilungen", "[name]="""
& [Name & """")

It is not I dont want to provide you the table and so on, but it is too
complicated to put all here.. I am really sorry, no offence..

[quoted text clipped - 94 lines]

using an inefficient expression in the name text box:
=DLookup("Name", "tblA", "Department='" & Department & "'")