Getting values from another table before entire record is saved

[piscogirl]

Hi All,

Here's my problem:

I'm using macro to do a Set Value in a field from another form. That
other form has to be open, and on the correct record, for the value to
appear correctly.

I thought I could start entering the record and then institute the
macro: include a "Save" line in the macro, sort by a time stamp and go
to the last record, and then open the other form to the correct record
based on what was open in my current form. Then I'd grab the values I
want and put them into my form, and close the other form.

But... it's consistently opening the record that was added before the
one I am currently adding, and setting the values to the ones on that
record, instead of the ones I want.

I've tried adding things like "GoTo New Record" and then "GoTo Last
Record" to try to trick Access into recognizing that the record I'm
working on should indeed be saved in the form's table. But, no luck.

To complicate matters, the form I have open is actually a subform, and
I have to include an OpenForm command in the macro for it even to
recognize that it's really being used. Could this be the source of my
problem?

My underlying goal is to set up a default value for the field which can
be overwritten manually. If changed, it should *not* change the value
in the other table. I'd welcome other ideas as to how to do this as
well.

Thanks so much, everybody!

- Melissa

 

[Guest]

I really do not use macros, but would do this with code instead. You could
us a dlookup in an event procedure to do this without having to open forms
and other things you are doing. You could use something like:

Me![ControlName] = dlookup("FieldName","TableName","[TableID] = " &
Me![ControlIDName])

The TableID and ControlIDName is the field and controls that are the link
between the records.

 

[piscogirl]

PERFECT!

(almost...)

here's my code:

Me![cboBPSSchoolDept] = DLookup("BPSSchoolDept", "tblPerson",
"[System_Person_ID]=[cboSystem_Person_ID]")

No '&Me!' before the ControlIDName

Thanks so much!

- Melissa

 

[Guest]

With your code it is literally looking for [System_Person_ID]=
[boSystem_Person_ID] not the value of [boSystem_Person_ID]. If
[boSystem_Person_ID] is a number then you will need

"[System_Person_ID]= " & Forms![FormName]![boSystem_Person_ID]

I replace the Me! with the Forms![FormName] . If it is text then you need

"[System_Person_ID]= '" & Forms![FormName]![boSystem_Person_ID]&"'"

PERFECT!

(almost...)

here's my code:

Me![cboBPSSchoolDept] = DLookup("BPSSchoolDept", "tblPerson",
"[System_Person_ID]=[cboSystem_Person_ID]")

No '&Me!' before the ControlIDName

Thanks so much!

- Melissa

I really do not use macros, but would do this with code instead. You could
us a dlookup in an event procedure to do this without having to open forms
and other things you are doing. You could use something like:

Me![ControlName] = dlookup("FieldName","TableName","[TableID] = " &
Me![ControlIDName])

The TableID and ControlIDName is the field and controls that are the link
between the records.

 

[piscogirl]

Hmmm... I'll give it a try that way, but it seems to be working the way
I have it set up right now.

[cboSystem_Person_ID] is actually a combo box looking up it's value
from the [System_Person_ID] field on tblPerson. Perhaps that's why
it's working.

With your code it is literally looking for [System_Person_ID]=
[boSystem_Person_ID] not the value of [boSystem_Person_ID]. If
[boSystem_Person_ID] is a number then you will need

"[System_Person_ID]= " & Forms![FormName]![boSystem_Person_ID]

I replace the Me! with the Forms![FormName] . If it is text then you need

"[System_Person_ID]= '" & Forms![FormName]![boSystem_Person_ID]&"'"

PERFECT!

(almost...)

here's my code:

Me![cboBPSSchoolDept] = DLookup("BPSSchoolDept", "tblPerson",
"[System_Person_ID]=[cboSystem_Person_ID]")

No '&Me!' before the ControlIDName

Thanks so much!

- Melissa

I really do not use macros, but would do this with code instead. You could
us a dlookup in an event procedure to do this without having to open forms
and other things you are doing. You could use something like:

Me![ControlName] = dlookup("FieldName","TableName","[TableID] = " &
Me![ControlIDName])

The TableID and ControlIDName is the field and controls that are the link
between the records.

 

[Guest]

If it is working than you should be OK and not need to change anything. I
thought you were having problems. Sorry for any confusion.

Hmmm... I'll give it a try that way, but it seems to be working the way
I have it set up right now.

[cboSystem_Person_ID] is actually a combo box looking up it's value
from the [System_Person_ID] field on tblPerson. Perhaps that's why
it's working.

With your code it is literally looking for [System_Person_ID]=
[boSystem_Person_ID] not the value of [boSystem_Person_ID]. If
[boSystem_Person_ID] is a number then you will need

"[System_Person_ID]= " & Forms![FormName]![boSystem_Person_ID]

I replace the Me! with the Forms![FormName] . If it is text then you need

"[System_Person_ID]= '" & Forms![FormName]![boSystem_Person_ID]&"'"

PERFECT!

(almost...)

here's my code:

Me![cboBPSSchoolDept] = DLookup("BPSSchoolDept", "tblPerson",
"[System_Person_ID]=[cboSystem_Person_ID]")

No '&Me!' before the ControlIDName

Thanks so much!

- Melissa

schasteen wrote:
I really do not use macros, but would do this with code instead. You could
us a dlookup in an event procedure to do this without having to open forms
and other things you are doing. You could use something like:

Me![ControlName] = dlookup("FieldName","TableName","[TableID] = " &
Me![ControlIDName])

The TableID and ControlIDName is the field and controls that are the link
between the records.

 

[piscogirl]

Thanks again for your help!

If it is working than you should be OK and not need to change anything. I
thought you were having problems. Sorry for any confusion.

Hmmm... I'll give it a try that way, but it seems to be working the way
I have it set up right now.

[cboSystem_Person_ID] is actually a combo box looking up it's value
from the [System_Person_ID] field on tblPerson. Perhaps that's why
it's working.

With your code it is literally looking for [System_Person_ID]=
[boSystem_Person_ID] not the value of [boSystem_Person_ID]. If
[boSystem_Person_ID] is a number then you will need

"[System_Person_ID]= " & Forms![FormName]![boSystem_Person_ID]

I replace the Me! with the Forms![FormName] . If it is text then you need

"[System_Person_ID]= '" & Forms![FormName]![boSystem_Person_ID]&"'"

:

PERFECT!

(almost...)

here's my code:

Me![cboBPSSchoolDept] = DLookup("BPSSchoolDept", "tblPerson",
"[System_Person_ID]=[cboSystem_Person_ID]")

No '&Me!' before the ControlIDName

Thanks so much!

- Melissa

schasteen wrote:
I really do not use macros, but would do this with code instead. You could
us a dlookup in an event procedure to do this without having to open forms
and other things you are doing. You could use something like:

Me![ControlName] = dlookup("FieldName","TableName","[TableID] = " &
Me![ControlIDName])

The TableID and ControlIDName is the field and controls that are the link
between the records.