Duplicate Data

[Mr-Re Man]

One main form with two subforms (linked by ProjectID), first subform is for
data entry, the second just allows the user to view what has been saved from
the previous subform.

The subform allows users to report on a month basis by selecting a month in
a combo box [PeriodID]. I would like to add some code in the Befire Update
event that checks the PeriodID for a match before it is saved.

Can someone help me with the code please, I have tried various DLookup
snippets of code from this site, but nothing is working as the code does not
really suit my purpose.

regards

 

[Maurice]

Maybe this could be one way;

in the before update of the record write the following:

dim rs as new adodb.recordset


rs.Open "select projectid from Your table where projectid=" & me.projectid,
CurrentProject.Connection, adOpenStatic, adLockOptimistic
With rs
if not rs.bof and not rs.eof then
'projectid is found so don't add it
else
'projectid not found add it
end if
.Close
End With
Set rs = Nothing

replace the appropriate fields and fieldnames with your own field and
tablenames and give it a try.

 

[Mr-Re Man]

Thanks Maurice, I have tried to fill in the blanks but am not getting
anywhere fast.

My table is called tblReporting, but the PeriodID comes from a joined table
called tblPeriods.

What do I put in the comnmented parts e.g. 'projectid is found so don't add it

I have tried tweaking this for a few hours with no effect.

Maybe this could be one way;

in the before update of the record write the following:

dim rs as new adodb.recordset


rs.Open "select projectid from Your table where projectid=" & me.projectid,
CurrentProject.Connection, adOpenStatic, adLockOptimistic
With rs
if not rs.bof and not rs.eof then
'projectid is found so don't add it
else
'projectid not found add it
end if
.Close
End With
Set rs = Nothing

replace the appropriate fields and fieldnames with your own field and
tablenames and give it a try.
--
Maurice Ausum

One main form with two subforms (linked by ProjectID), first subform is for
data entry, the second just allows the user to view what has been saved from
the previous subform.

The subform allows users to report on a month basis by selecting a month in
a combo box [PeriodID]. I would like to add some code in the Befire Update
event that checks the PeriodID for a match before it is saved.

Can someone help me with the code please, I have tried various DLookup
snippets of code from this site, but nothing is working as the code does not
really suit my purpose.

regards

 

[Maurice]

rs.Open "select projectid from tblPeriods where projectid=" & me.projectid

I've placed your tablename in the open statement above. I assume the
fieldnames are correct right? Id field is called ProjectID en field on form
is also called ProjectID.

The commented part does nothing because you stated in your first post that
you wanted to check if it exists so that's what we're doing. In stead of the
commented part you can write your own actions like say a messagebox stating
that the record already exists or save the record, whatever you had in mind
after validating if the record existed yet.

--
Maurice Ausum

Thanks Maurice, I have tried to fill in the blanks but am not getting
anywhere fast.

My table is called tblReporting, but the PeriodID comes from a joined table
called tblPeriods.

What do I put in the comnmented parts e.g. 'projectid is found so don't add it

I have tried tweaking this for a few hours with no effect.

Maybe this could be one way;

in the before update of the record write the following:

dim rs as new adodb.recordset


rs.Open "select projectid from Your table where projectid=" & me.projectid,
CurrentProject.Connection, adOpenStatic, adLockOptimistic
With rs
if not rs.bof and not rs.eof then
'projectid is found so don't add it
else
'projectid not found add it
end if
.Close
End With
Set rs = Nothing

replace the appropriate fields and fieldnames with your own field and
tablenames and give it a try.
--
Maurice Ausum

One main form with two subforms (linked by ProjectID), first subform is for
data entry, the second just allows the user to view what has been saved from
the previous subform.

The subform allows users to report on a month basis by selecting a month in
a combo box [PeriodID]. I would like to add some code in the Befire Update
event that checks the PeriodID for a match before it is saved.

Can someone help me with the code please, I have tried various DLookup
snippets of code from this site, but nothing is working as the code does not
really suit my purpose.

regards

 

[Mr-Re Man]

I changed the code as suggested but noticed that ProjectID doesn't live in
the tblPeriods. They are linked via child (sub form) and master (main form)
by ProjectID.

On the child (sub form) "subqryReporting", PeriodID and ProjectID are joined
in here from various tables.

My code at the moment is this:
rs.Open "select PeriodID from subqryReporting where ProjectID=" & Me.ProjectID

but the error message I recive is on the next line:
CurrentProject.Connection , adOpenStatic, adLockOptimistic

where XxxxxxXxxxxx.Connection, xxXxxxXxxxxx, xxXxxxXxxxxxxxx (connection bit
is highlighted) and the error box is as follows:

Compile Error: invalid use of property

thanks again Maurice

rs.Open "select projectid from tblPeriods where projectid=" & me.projectid

I've placed your tablename in the open statement above. I assume the
fieldnames are correct right? Id field is called ProjectID en field on form
is also called ProjectID.

The commented part does nothing because you stated in your first post that
you wanted to check if it exists so that's what we're doing. In stead of the
commented part you can write your own actions like say a messagebox stating
that the record already exists or save the record, whatever you had in mind
after validating if the record existed yet.

--
Maurice Ausum

Thanks Maurice, I have tried to fill in the blanks but am not getting
anywhere fast.

My table is called tblReporting, but the PeriodID comes from a joined table
called tblPeriods.

What do I put in the comnmented parts e.g. 'projectid is found so don't add it

I have tried tweaking this for a few hours with no effect.

Maybe this could be one way;

in the before update of the record write the following:

dim rs as new adodb.recordset


rs.Open "select projectid from Your table where projectid=" & me.projectid,
CurrentProject.Connection, adOpenStatic, adLockOptimistic
With rs
if not rs.bof and not rs.eof then
'projectid is found so don't add it
else
'projectid not found add it
end if
.Close
End With
Set rs = Nothing

replace the appropriate fields and fieldnames with your own field and
tablenames and give it a try.
--
Maurice Ausum


:

One main form with two subforms (linked by ProjectID), first subform is for
data entry, the second just allows the user to view what has been saved from
the previous subform.

The subform allows users to report on a month basis by selecting a month in
a combo box [PeriodID]. I would like to add some code in the Befire Update
event that checks the PeriodID for a match before it is saved.

Can someone help me with the code please, I have tried various DLookup
snippets of code from this site, but nothing is working as the code does not
really suit my purpose.

regards

 

[Maurice]

Ok, now we are getting somewhere

What version of access are you using?

You can also use the dao version instead of the ADO version

the it will look like this

dim db as dao.database
dim rs as dao.recordset

set db=currentdb()
set rs= db.openrecordset("subqryReporting")

the rest stays the same.

ok now let's try this...
--
Maurice Ausum

I changed the code as suggested but noticed that ProjectID doesn't live in
the tblPeriods. They are linked via child (sub form) and master (main form)
by ProjectID.

On the child (sub form) "subqryReporting", PeriodID and ProjectID are joined
in here from various tables.

My code at the moment is this:
rs.Open "select PeriodID from subqryReporting where ProjectID=" & Me.ProjectID

but the error message I recive is on the next line:
CurrentProject.Connection , adOpenStatic, adLockOptimistic

where XxxxxxXxxxxx.Connection, xxXxxxXxxxxx, xxXxxxXxxxxxxxx (connection bit
is highlighted) and the error box is as follows:

Compile Error: invalid use of property

thanks again Maurice

rs.Open "select projectid from tblPeriods where projectid=" & me.projectid

I've placed your tablename in the open statement above. I assume the
fieldnames are correct right? Id field is called ProjectID en field on form
is also called ProjectID.

The commented part does nothing because you stated in your first post that
you wanted to check if it exists so that's what we're doing. In stead of the
commented part you can write your own actions like say a messagebox stating
that the record already exists or save the record, whatever you had in mind
after validating if the record existed yet.

--
Maurice Ausum

Thanks Maurice, I have tried to fill in the blanks but am not getting
anywhere fast.

My table is called tblReporting, but the PeriodID comes from a joined table
called tblPeriods.

What do I put in the comnmented parts e.g. 'projectid is found so don't add it

I have tried tweaking this for a few hours with no effect.

:

Maybe this could be one way;

in the before update of the record write the following:

dim rs as new adodb.recordset


rs.Open "select projectid from Your table where projectid=" & me.projectid,
CurrentProject.Connection, adOpenStatic, adLockOptimistic
With rs
if not rs.bof and not rs.eof then
'projectid is found so don't add it
else
'projectid not found add it
end if
.Close
End With
Set rs = Nothing

replace the appropriate fields and fieldnames with your own field and
tablenames and give it a try.
--
Maurice Ausum


:

One main form with two subforms (linked by ProjectID), first subform is for
data entry, the second just allows the user to view what has been saved from
the previous subform.

The subform allows users to report on a month basis by selecting a month in
a combo box [PeriodID]. I would like to add some code in the Befire Update
event that checks the PeriodID for a match before it is saved.

Can someone help me with the code please, I have tried various DLookup
snippets of code from this site, but nothing is working as the code does not
really suit my purpose.

regards

 

[Mr-Re Man]

I have replaced the data as suggest so my code now reads

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("subqryReporting")

rs.Open "select PeriodID from subqryReporting where ProjectID=" & Me.ProjectID
CurrentProject.Connection , adOpenStatic, adLockOptimistic
With rs
If Not rs.BOF And Not rs.EOF Then
'projectid is found so don't add it
Else
'projectid not found add it
End If
.Close
End With
Set rs = Nothing
End Sub

The error highlights the last Me.ProjectID as Compile Error: Method or Data
member not found.

Does it matter if this subform is in Data Entry mode? can it read all the
other records in the query?

I feel we are close (you) to resolving this

Ok, now we are getting somewhere

What version of access are you using?

You can also use the dao version instead of the ADO version

the it will look like this

dim db as dao.database
dim rs as dao.recordset

set db=currentdb()
set rs= db.openrecordset("subqryReporting")

the rest stays the same.

ok now let's try this...
--
Maurice Ausum

I changed the code as suggested but noticed that ProjectID doesn't live in
the tblPeriods. They are linked via child (sub form) and master (main form)
by ProjectID.

On the child (sub form) "subqryReporting", PeriodID and ProjectID are joined
in here from various tables.

My code at the moment is this:
rs.Open "select PeriodID from subqryReporting where ProjectID=" & Me.ProjectID

but the error message I recive is on the next line:
CurrentProject.Connection , adOpenStatic, adLockOptimistic

where XxxxxxXxxxxx.Connection, xxXxxxXxxxxx, xxXxxxXxxxxxxxx (connection bit
is highlighted) and the error box is as follows:

Compile Error: invalid use of property

thanks again Maurice

rs.Open "select projectid from tblPeriods where projectid=" & me.projectid

I've placed your tablename in the open statement above. I assume the
fieldnames are correct right? Id field is called ProjectID en field on form
is also called ProjectID.

The commented part does nothing because you stated in your first post that
you wanted to check if it exists so that's what we're doing. In stead of the
commented part you can write your own actions like say a messagebox stating
that the record already exists or save the record, whatever you had in mind
after validating if the record existed yet.

--
Maurice Ausum


:

Thanks Maurice, I have tried to fill in the blanks but am not getting
anywhere fast.

My table is called tblReporting, but the PeriodID comes from a joined table
called tblPeriods.

What do I put in the comnmented parts e.g. 'projectid is found so don't add it

I have tried tweaking this for a few hours with no effect.

:

Maybe this could be one way;

in the before update of the record write the following:

dim rs as new adodb.recordset


rs.Open "select projectid from Your table where projectid=" & me.projectid,
CurrentProject.Connection, adOpenStatic, adLockOptimistic
With rs
if not rs.bof and not rs.eof then
'projectid is found so don't add it
else
'projectid not found add it
end if
.Close
End With
Set rs = Nothing

replace the appropriate fields and fieldnames with your own field and
tablenames and give it a try.
--
Maurice Ausum


:

One main form with two subforms (linked by ProjectID), first subform is for
data entry, the second just allows the user to view what has been saved from
the previous subform.

The subform allows users to report on a month basis by selecting a month in
a combo box [PeriodID]. I would like to add some code in the Befire Update
event that checks the PeriodID for a match before it is saved.

Can someone help me with the code please, I have tried various DLookup
snippets of code from this site, but nothing is working as the code does not
really suit my purpose.

regards

 

[John W. Vinson]

The error highlights the last Me.ProjectID as Compile Error: Method or Data
member not found.

THat means that there is no control on the Form named ProjectID, and also
there is no field in the form's Recordsource named ProjectID.

 

[Maurice]

hmm,

Just a correction here;

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("subqryReporting")

rs.Open "select PeriodID from subqryReporting where ProjectID=" & Me.ProjectID
With rs
If Not rs.BOF And Not rs.EOF Then
'projectid is found so don't add it
Else
'projectid not found add it
End If
.Close
End With
Set rs = Nothing
End Sub

It is telling you that it can't find the me.project control. What is the
name of the control you are referring to. In other words where does the
projectid come from? Do you have a textbox with a projectid in your form and
is this textbox called projectid?

Go in design and check the name of the control that supplies the projectid.
Place that name in the me.projectid piece of the code where we open the
recordset.

let me know...

--
Maurice Ausum

I have replaced the data as suggest so my code now reads

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("subqryReporting")

rs.Open "select PeriodID from subqryReporting where ProjectID=" & Me.ProjectID
CurrentProject.Connection , adOpenStatic, adLockOptimistic
With rs
If Not rs.BOF And Not rs.EOF Then
'projectid is found so don't add it
Else
'projectid not found add it
End If
.Close
End With
Set rs = Nothing
End Sub

The error highlights the last Me.ProjectID as Compile Error: Method or Data
member not found.

Does it matter if this subform is in Data Entry mode? can it read all the
other records in the query?

I feel we are close (you) to resolving this

Ok, now we are getting somewhere

What version of access are you using?

You can also use the dao version instead of the ADO version

the it will look like this

dim db as dao.database
dim rs as dao.recordset

set db=currentdb()
set rs= db.openrecordset("subqryReporting")

the rest stays the same.

ok now let's try this...
--
Maurice Ausum

I changed the code as suggested but noticed that ProjectID doesn't live in
the tblPeriods. They are linked via child (sub form) and master (main form)
by ProjectID.

On the child (sub form) "subqryReporting", PeriodID and ProjectID are joined
in here from various tables.

My code at the moment is this:
rs.Open "select PeriodID from subqryReporting where ProjectID=" & Me.ProjectID

but the error message I recive is on the next line:
CurrentProject.Connection , adOpenStatic, adLockOptimistic

where XxxxxxXxxxxx.Connection, xxXxxxXxxxxx, xxXxxxXxxxxxxxx (connection bit
is highlighted) and the error box is as follows:

Compile Error: invalid use of property

thanks again Maurice




:

rs.Open "select projectid from tblPeriods where projectid=" & me.projectid

I've placed your tablename in the open statement above. I assume the
fieldnames are correct right? Id field is called ProjectID en field on form
is also called ProjectID.

The commented part does nothing because you stated in your first post that
you wanted to check if it exists so that's what we're doing. In stead of the
commented part you can write your own actions like say a messagebox stating
that the record already exists or save the record, whatever you had in mind
after validating if the record existed yet.

--
Maurice Ausum


:

Thanks Maurice, I have tried to fill in the blanks but am not getting
anywhere fast.

My table is called tblReporting, but the PeriodID comes from a joined table
called tblPeriods.

What do I put in the comnmented parts e.g. 'projectid is found so don't add it

I have tried tweaking this for a few hours with no effect.

:

Maybe this could be one way;

in the before update of the record write the following:

dim rs as new adodb.recordset


rs.Open "select projectid from Your table where projectid=" & me.projectid,
CurrentProject.Connection, adOpenStatic, adLockOptimistic
With rs
if not rs.bof and not rs.eof then
'projectid is found so don't add it
else
'projectid not found add it
end if
.Close
End With
Set rs = Nothing

replace the appropriate fields and fieldnames with your own field and
tablenames and give it a try.
--
Maurice Ausum


:

One main form with two subforms (linked by ProjectID), first subform is for
data entry, the second just allows the user to view what has been saved from
the previous subform.

The subform allows users to report on a month basis by selecting a month in
a combo box [PeriodID]. I would like to add some code in the Befire Update
event that checks the PeriodID for a match before it is saved.

Can someone help me with the code please, I have tried various DLookup
snippets of code from this site, but nothing is working as the code does not
really suit my purpose.

regards

 

[Mr-Re Man]

Hi Maurice, updated code, exactly the same error message when I select and
try to add the same month with an idetnical ID.

The query called subqryReporting has the following fields in it.
ReportingID (Table: tblReporting)
ProjectID (Table: tblReporting)
PeriodID (Table: tblReporting)
as well as some other minor fields like budget estimate etc

The subforms Child & Master point to ProjectID and I have also made
ProjectID visible on the form so I can see it get populate once the
userstarts to type.

I think I can follow whats going on but am puzzled to why it isn't working?

We'll get there!

hmm,

Just a correction here;

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("subqryReporting")

rs.Open "select PeriodID from subqryReporting where ProjectID=" & Me.ProjectID
With rs
If Not rs.BOF And Not rs.EOF Then
'projectid is found so don't add it
Else
'projectid not found add it
End If
.Close
End With
Set rs = Nothing
End Sub

It is telling you that it can't find the me.project control. What is the
name of the control you are referring to. In other words where does the
projectid come from? Do you have a textbox with a projectid in your form and
is this textbox called projectid?

Go in design and check the name of the control that supplies the projectid.
Place that name in the me.projectid piece of the code where we open the
recordset.

let me know...

--
Maurice Ausum

I have replaced the data as suggest so my code now reads

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("subqryReporting")

rs.Open "select PeriodID from subqryReporting where ProjectID=" & Me.ProjectID
CurrentProject.Connection , adOpenStatic, adLockOptimistic
With rs
If Not rs.BOF And Not rs.EOF Then
'projectid is found so don't add it
Else
'projectid not found add it
End If
.Close
End With
Set rs = Nothing
End Sub

The error highlights the last Me.ProjectID as Compile Error: Method or Data
member not found.

Does it matter if this subform is in Data Entry mode? can it read all the
other records in the query?

I feel we are close (you) to resolving this

Ok, now we are getting somewhere

What version of access are you using?

You can also use the dao version instead of the ADO version

the it will look like this

dim db as dao.database
dim rs as dao.recordset

set db=currentdb()
set rs= db.openrecordset("subqryReporting")

the rest stays the same.

ok now let's try this...
--
Maurice Ausum


:

I changed the code as suggested but noticed that ProjectID doesn't live in
the tblPeriods. They are linked via child (sub form) and master (main form)
by ProjectID.

On the child (sub form) "subqryReporting", PeriodID and ProjectID are joined
in here from various tables.

My code at the moment is this:
rs.Open "select PeriodID from subqryReporting where ProjectID=" & Me.ProjectID

but the error message I recive is on the next line:
CurrentProject.Connection , adOpenStatic, adLockOptimistic

where XxxxxxXxxxxx.Connection, xxXxxxXxxxxx, xxXxxxXxxxxxxxx (connection bit
is highlighted) and the error box is as follows:

Compile Error: invalid use of property

thanks again Maurice




:

rs.Open "select projectid from tblPeriods where projectid=" & me.projectid

I've placed your tablename in the open statement above. I assume the
fieldnames are correct right? Id field is called ProjectID en field on form
is also called ProjectID.

The commented part does nothing because you stated in your first post that
you wanted to check if it exists so that's what we're doing. In stead of the
commented part you can write your own actions like say a messagebox stating
that the record already exists or save the record, whatever you had in mind
after validating if the record existed yet.

--
Maurice Ausum


:

Thanks Maurice, I have tried to fill in the blanks but am not getting
anywhere fast.

My table is called tblReporting, but the PeriodID comes from a joined table
called tblPeriods.

What do I put in the comnmented parts e.g. 'projectid is found so don't add it

I have tried tweaking this for a few hours with no effect.

:

Maybe this could be one way;

in the before update of the record write the following:

dim rs as new adodb.recordset


rs.Open "select projectid from Your table where projectid=" & me.projectid,
CurrentProject.Connection, adOpenStatic, adLockOptimistic
With rs
if not rs.bof and not rs.eof then
'projectid is found so don't add it
else
'projectid not found add it
end if
.Close
End With
Set rs = Nothing

replace the appropriate fields and fieldnames with your own field and
tablenames and give it a try.
--
Maurice Ausum


:

One main form with two subforms (linked by ProjectID), first subform is for
data entry, the second just allows the user to view what has been saved from
the previous subform.

The subform allows users to report on a month basis by selecting a month in
a combo box [PeriodID]. I would like to add some code in the Befire Update
event that checks the PeriodID for a match before it is saved.

Can someone help me with the code please, I have tried various DLookup
snippets of code from this site, but nothing is working as the code does not
really suit my purpose.

regards