Default Value & Data from another Table

[Wingnut]

Our laboratory uses Access to track test results. I am trying to eliminate
some of the steps we go thru each time we enter new data by configuring
Access to populate some specific fields.

I have 2 tables I'm trying to work with. The first table (tblUPCs) contains
a UPC number which is associated to a bunch of other information including a
field that contains the number of days from the production date until the
expiration.

The second table (tblMain) has all the other information that must be
manually entered. What I would like to do is create an expression for the
Default Value in this table which will take the number of days corresponding
to the UPC we scan in and then add that number to the current date.

I'm fairly new to access so I'm open to making some changes to the structure
of the database. I'm fairly confident that my structure is fine I'm just
lacking in the ability to program. I've read some of the previous responses
to similar questions and I don't feel like they help in this circumstance.
They weren't trying to pull data from a field in an associated table.

 

[schasteen]

I would suggest in the after update event of the Upc Code, you use Dlookup
and DateAdd functions. This is not tested so there could be typos, but with
the help menus you should be OK

Dim NumberOfDays as Integer

NumberOfDays = Dlookup("[Field in table]","tblUPCs","UpcCode = '" &
Me!Upccode & " ' ")

Me!ExpirationDate = DateAdd("d",NumberOfDays,Date())

You will need to change this to match your field’s name

 

[Wingnut]

I appreciate the help. I see how it should work and I think it will work but
when I debug I keep getting a 'Compile error: Invalid use of Me keyword.'


Dim NumberOfDays As Integer
NumberOfDays = DLookup("[UPC]", "tblUPCs", "[Outdate] = '" & Me!Outdate & "'")
Me!CodeDate = DateAdd("d", NumberOfDays, Date)â€

I would suggest in the after update event of the Upc Code, you use Dlookup
and DateAdd functions. This is not tested so there could be typos, but with
the help menus you should be OK

Dim NumberOfDays as Integer

NumberOfDays = Dlookup("[Field in table]","tblUPCs","UpcCode = '" &
Me!Upccode & " ' ")

Me!ExpirationDate = DateAdd("d",NumberOfDays,Date())

You will need to change this to match your field’s name

Our laboratory uses Access to track test results. I am trying to eliminate
some of the steps we go thru each time we enter new data by configuring
Access to populate some specific fields.

I have 2 tables I'm trying to work with. The first table (tblUPCs) contains
a UPC number which is associated to a bunch of other information including a
field that contains the number of days from the production date until the
expiration.

The second table (tblMain) has all the other information that must be
manually entered. What I would like to do is create an expression for the
Default Value in this table which will take the number of days corresponding
to the UPC we scan in and then add that number to the current date.

I'm fairly new to access so I'm open to making some changes to the structure
of the database. I'm fairly confident that my structure is fine I'm just
lacking in the ability to program. I've read some of the previous responses
to similar questions and I don't feel like they help in this circumstance.
They weren't trying to pull data from a field in an associated table.

 

[schasteen]

Me is a referance to the current for instead of using
Forms![FormName]![ControlName]. So it is strange that it is giving an error.
Where are you placing the code?

I appreciate the help. I see how it should work and I think it will work but
when I debug I keep getting a 'Compile error: Invalid use of Me keyword.'


Dim NumberOfDays As Integer
NumberOfDays = DLookup("[UPC]", "tblUPCs", "[Outdate] = '" & Me!Outdate & "'")
Me!CodeDate = DateAdd("d", NumberOfDays, Date)â€

I would suggest in the after update event of the Upc Code, you use Dlookup
and DateAdd functions. This is not tested so there could be typos, but with
the help menus you should be OK

Dim NumberOfDays as Integer

NumberOfDays = Dlookup("[Field in table]","tblUPCs","UpcCode = '" &
Me!Upccode & " ' ")

Me!ExpirationDate = DateAdd("d",NumberOfDays,Date())

You will need to change this to match your field’s name

Our laboratory uses Access to track test results. I am trying to eliminate
some of the steps we go thru each time we enter new data by configuring
Access to populate some specific fields.

I have 2 tables I'm trying to work with. The first table (tblUPCs) contains
a UPC number which is associated to a bunch of other information including a
field that contains the number of days from the production date until the
expiration.

The second table (tblMain) has all the other information that must be
manually entered. What I would like to do is create an expression for the
Default Value in this table which will take the number of days corresponding
to the UPC we scan in and then add that number to the current date.

I'm fairly new to access so I'm open to making some changes to the structure
of the database. I'm fairly confident that my structure is fine I'm just
lacking in the ability to program. I've read some of the previous responses
to similar questions and I don't feel like they help in this circumstance.
They weren't trying to pull data from a field in an associated table.

 

[Wingnut]

I'm placing the code in the AfterUpdate field of the Form where the UPC is
entered.

Just as FYI I am using Access 07
I appreciate the help.

Me is a reference to the current form instead of using
Forms![FormName]![ControlName]. So it is strange that it is giving an error.
Where are you placing the code?

I appreciate the help. I see how it should work and I think it will work but
when I debug I keep getting a 'Compile error: Invalid use of Me keyword.'


Dim NumberOfDays As Integer
NumberOfDays = DLookup("[UPC]", "tblUPCs", "[Outdate] = '" & Me!Outdate & "'")
Me!CodeDate = DateAdd("d", NumberOfDays, Date)â€

I would suggest in the after update event of the Upc Code, you use Dlookup
and DateAdd functions. This is not tested so there could be typos, but with
the help menus you should be OK

Dim NumberOfDays as Integer

NumberOfDays = Dlookup("[Field in table]","tblUPCs","UpcCode = '" &
Me!Upccode & " ' ")

Me!ExpirationDate = DateAdd("d",NumberOfDays,Date())

You will need to change this to match your field’s name

:

Our laboratory uses Access to track test results. I am trying to eliminate
some of the steps we go thru each time we enter new data by configuring
Access to populate some specific fields.

I have 2 tables I'm trying to work with. The first table (tblUPCs) contains
a UPC number which is associated to a bunch of other information including a
field that contains the number of days from the production date until the
expiration.

The second table (tblMain) has all the other information that must be
manually entered. What I would like to do is create an expression for the
Default Value in this table which will take the number of days corresponding
to the UPC we scan in and then add that number to the current date.

I'm fairly new to access so I'm open to making some changes to the structure
of the database. I'm fairly confident that my structure is fine I'm just
lacking in the ability to program. I've read some of the previous responses
to similar questions and I don't feel like they help in this circumstance.
They weren't trying to pull data from a field in an associated table.

 

[schasteen]

Is your codeDate control on the same form? Also, I may be misunderstanding
your naming system, but your Dlookup statement appears to be looking for the
UPC.


NumberOfDays = DLookup("[UPC]", "tblUPCs", "[Outdate] = '" & Me!Outdate & "'")

From my understanding you should have

NumberOfDays = DLookup("[Outdate]", "tblUPCs", "[UPC] = '" &
[UPCControlName]& "'")

Also, the above expression is assuming that the UPC code is stored as text,
if it is an integer or other number type you would have

NumberOfDays = DLookup("[Outdate]", "tblUPCs", "[UPC] = " &
Me![UPCControlName])

I'm placing the code in the AfterUpdate field of the Form where the UPC is
entered.

Just as FYI I am using Access 07
I appreciate the help.

Me is a reference to the current form instead of using
Forms![FormName]![ControlName]. So it is strange that it is giving an error.
Where are you placing the code?

I appreciate the help. I see how it should work and I think it will work but
when I debug I keep getting a 'Compile error: Invalid use of Me keyword.'


Dim NumberOfDays As Integer
NumberOfDays = DLookup("[UPC]", "tblUPCs", "[Outdate] = '" & Me!Outdate & "'")
Me!CodeDate = DateAdd("d", NumberOfDays, Date)â€


:

I would suggest in the after update event of the Upc Code, you use Dlookup
and DateAdd functions. This is not tested so there could be typos, but with
the help menus you should be OK

Dim NumberOfDays as Integer

NumberOfDays = Dlookup("[Field in table]","tblUPCs","UpcCode = '" &
Me!Upccode & " ' ")

Me!ExpirationDate = DateAdd("d",NumberOfDays,Date())

You will need to change this to match your field’s name

:

Our laboratory uses Access to track test results. I am trying to eliminate
some of the steps we go thru each time we enter new data by configuring
Access to populate some specific fields.

I have 2 tables I'm trying to work with. The first table (tblUPCs) contains
a UPC number which is associated to a bunch of other information including a
field that contains the number of days from the production date until the
expiration.

The second table (tblMain) has all the other information that must be
manually entered. What I would like to do is create an expression for the
Default Value in this table which will take the number of days corresponding
to the UPC we scan in and then add that number to the current date.

I'm fairly new to access so I'm open to making some changes to the structure
of the database. I'm fairly confident that my structure is fine I'm just
lacking in the ability to program. I've read some of the previous responses
to similar questions and I don't feel like they help in this circumstance.
They weren't trying to pull data from a field in an associated table.

 

[Wingnut]

Ok. So I got it to work. I made a beginner's mistake (which I am) I didn't
select the right Object for the code.

Now I have another question... In the DateAdd() part I realized we are
taking the current date Date() and then adding the number of days until
expiration. Is it possible to insert the value from another field into that
part of the function? For instance take the sample date and then add the
expiration days to that?

Here is what I think it would look like. I'm learning but I'm very ignorant
when it comes to VBA. Do I need to create this DateSampleTaken in another
sub?

Dim NumberOfDays As Integer
Dim DateSampleTaken As Date

NumberOfDays = DLookup("[Outdate]", "tblUPCs", "[UPC] = " & Me![UPC])
DateSampleTaken = DLookup("[SampleDate]", "tblMain", "SampleDate = '")

Me!CodeDate = DateAdd("d", NumberOfDays, DateSampleTaken)

 

[Wingnut]

NVM...

I got it to work with this.

Private Sub UPC_AfterUpdate()

Dim NumberOfDays As Integer
Dim DateSampleTaken As Date

NumberOfDays = DLookup("[Outdate]", "tblUPCs", "[UPC] = " & Me![UPC])
DateSampleTaken = DLookup("[SampleDate]", "tblMain", "[UPC] = " & Me![UPC])

Me!CodeDate = DateAdd("d", NumberOfDays, DateSampleTaken)

End Sub

Is your codeDate control on the same form? Also, I may be misunderstanding
your naming system, but your Dlookup statement appears to be looking for the
UPC.


NumberOfDays = DLookup("[UPC]", "tblUPCs", "[Outdate] = '" & Me!Outdate & "'")

From my understanding you should have

NumberOfDays = DLookup("[Outdate]", "tblUPCs", "[UPC] = '" &
[UPCControlName]& "'")

Also, the above expression is assuming that the UPC code is stored as text,
if it is an integer or other number type you would have

NumberOfDays = DLookup("[Outdate]", "tblUPCs", "[UPC] = " &
Me![UPCControlName])

I'm placing the code in the AfterUpdate field of the Form where the UPC is
entered.

Just as FYI I am using Access 07
I appreciate the help.

Me is a reference to the current form instead of using
Forms![FormName]![ControlName]. So it is strange that it is giving an error.
Where are you placing the code?

:

I appreciate the help. I see how it should work and I think it will work but
when I debug I keep getting a 'Compile error: Invalid use of Me keyword.'


Dim NumberOfDays As Integer
NumberOfDays = DLookup("[UPC]", "tblUPCs", "[Outdate] = '" & Me!Outdate & "'")
Me!CodeDate = DateAdd("d", NumberOfDays, Date)â€


:

I would suggest in the after update event of the Upc Code, you use Dlookup
and DateAdd functions. This is not tested so there could be typos, but with
the help menus you should be OK

Dim NumberOfDays as Integer

NumberOfDays = Dlookup("[Field in table]","tblUPCs","UpcCode = '" &
Me!Upccode & " ' ")

Me!ExpirationDate = DateAdd("d",NumberOfDays,Date())

You will need to change this to match your field’s name

:

Our laboratory uses Access to track test results. I am trying to eliminate
some of the steps we go thru each time we enter new data by configuring
Access to populate some specific fields.

I have 2 tables I'm trying to work with. The first table (tblUPCs) contains
a UPC number which is associated to a bunch of other information including a
field that contains the number of days from the production date until the
expiration.

The second table (tblMain) has all the other information that must be
manually entered. What I would like to do is create an expression for the
Default Value in this table which will take the number of days corresponding
to the UPC we scan in and then add that number to the current date.

I'm fairly new to access so I'm open to making some changes to the structure
of the database. I'm fairly confident that my structure is fine I'm just
lacking in the ability to program. I've read some of the previous responses
to similar questions and I don't feel like they help in this circumstance.
They weren't trying to pull data from a field in an associated table.

 

[schasteen]

Only issue you will have is if those values are ever null in your table
(Outdate,SampleDate). If they are required entry you will be OK, if not you
can use the Nz function. For example

DateSampleTaken = Nz(DLookup("[SampleDate]", "tblMain", "[UPC] = " &
Me![UPC]),Date)

NVM...

I got it to work with this.

Private Sub UPC_AfterUpdate()

Dim NumberOfDays As Integer
Dim DateSampleTaken As Date

NumberOfDays = DLookup("[Outdate]", "tblUPCs", "[UPC] = " & Me![UPC])
DateSampleTaken = DLookup("[SampleDate]", "tblMain", "[UPC] = " & Me![UPC])

Me!CodeDate = DateAdd("d", NumberOfDays, DateSampleTaken)

End Sub

Is your codeDate control on the same form? Also, I may be misunderstanding
your naming system, but your Dlookup statement appears to be looking for the
UPC.


NumberOfDays = DLookup("[UPC]", "tblUPCs", "[Outdate] = '" & Me!Outdate & "'")

From my understanding you should have

NumberOfDays = DLookup("[Outdate]", "tblUPCs", "[UPC] = '" &
[UPCControlName]& "'")

Also, the above expression is assuming that the UPC code is stored as text,
if it is an integer or other number type you would have

NumberOfDays = DLookup("[Outdate]", "tblUPCs", "[UPC] = " &
Me![UPCControlName])

I'm placing the code in the AfterUpdate field of the Form where the UPC is
entered.

Just as FYI I am using Access 07
I appreciate the help.

:

Me is a reference to the current form instead of using
Forms![FormName]![ControlName]. So it is strange that it is giving an error.
Where are you placing the code?

:

I appreciate the help. I see how it should work and I think it will work but
when I debug I keep getting a 'Compile error: Invalid use of Me keyword.'


Dim NumberOfDays As Integer
NumberOfDays = DLookup("[UPC]", "tblUPCs", "[Outdate] = '" & Me!Outdate & "'")
Me!CodeDate = DateAdd("d", NumberOfDays, Date)â€


:

I would suggest in the after update event of the Upc Code, you use Dlookup
and DateAdd functions. This is not tested so there could be typos, but with
the help menus you should be OK

Dim NumberOfDays as Integer

NumberOfDays = Dlookup("[Field in table]","tblUPCs","UpcCode = '" &
Me!Upccode & " ' ")

Me!ExpirationDate = DateAdd("d",NumberOfDays,Date())

You will need to change this to match your field’s name

:

Our laboratory uses Access to track test results. I am trying to eliminate
some of the steps we go thru each time we enter new data by configuring
Access to populate some specific fields.

I have 2 tables I'm trying to work with. The first table (tblUPCs) contains
a UPC number which is associated to a bunch of other information including a
field that contains the number of days from the production date until the
expiration.

The second table (tblMain) has all the other information that must be
manually entered. What I would like to do is create an expression for the
Default Value in this table which will take the number of days corresponding
to the UPC we scan in and then add that number to the current date.

I'm fairly new to access so I'm open to making some changes to the structure
of the database. I'm fairly confident that my structure is fine I'm just
lacking in the ability to program. I've read some of the previous responses
to similar questions and I don't feel like they help in this circumstance.
They weren't trying to pull data from a field in an associated table.