FindRecord (Macro) and then add one for a new record? (resend)

[Steve Schapel]

Tammy,

Ok, so this procedure is happening on the After Update event of the
BaseNumber on your form, right? So in other words it gets triggered
once you enter something in the BaseNumber combobox. So maybe this
error means there is currently no records in the Product table with a
BaseNumber as entered in the BaseNumber combobox on the form? Or, if
there are, there are none with anything at all in the Extension field?
So, if this is the case, I guess we could handle it by catering to this
possibility via the use of the Nz() function, what do you think?
NextNumber = Nz(DMax("[Extension]", "Product", "[BaseNumber]='" &
Me.BASENUMBER & "'"), 0) + 1

 

[Steve Schapel]

Tammy,

On reviewing the progress so far, it has occurred to me that we didn't
specifically mention the step in the process of dealing with your
existing data. I am not sure what you are using for your testing
purposes. But I have assumed it would be obvious to you that you would
need to "split" the data in your original ill-conceived composite field
into the two new fields for BaseNumber and Extension. Is this ok?

 

[Guest]

Hi Steve,
What I did is created a new table for testing, so it doesnt have any of the
existing information in it, and doesnt change my working tables or forms.
This table only has the two fields BaseNumber and Extension.

Yes I will be spliting these and deleting the field that has the combined
number.

Thanks,

Tammy,

On reviewing the progress so far, it has occurred to me that we didn't
specifically mention the step in the process of dealing with your
existing data. I am not sure what you are using for your testing
purposes. But I have assumed it would be obvious to you that you would
need to "split" the data in your original ill-conceived composite field
into the two new fields for BaseNumber and Extension. Is this ok?

--
Steve Schapel, Microsoft Access MVP

Tammy,

Ok, so this procedure is happening on the After Update event of the
BaseNumber on your form, right? So in other words it gets triggered
once you enter something in the BaseNumber combobox. So maybe this
error means there is currently no records in the Product table with a
BaseNumber as entered in the BaseNumber combobox on the form? Or, if
there are, there are none with anything at all in the Extension field?
So, if this is the case, I guess we could handle it by catering to this
possibility via the use of the Nz() function, what do you think?
NextNumber = Nz(DMax("[Extension]", "Product", "[BaseNumber]='" &
Me.BASENUMBER & "'"), 0) + 1

 

[Guest]

Okay I tried:

NextNumber = Nz(DMax("[Extension]", "Product", "[BaseNumber]='" &
Me.BASENUMBER & "'"), 0) + 1

But extension is not incrementing by one, it just stays at 0. What I am
doing is typing a new product number into the basenumber combo box and
pressing enter, but the extension is staying at 0.

You must be getting tired of me Hopefully this will also help someone
else too.

Thanks,
Tammy

Tammy,

Ok, so this procedure is happening on the After Update event of the
BaseNumber on your form, right? So in other words it gets triggered
once you enter something in the BaseNumber combobox. So maybe this
error means there is currently no records in the Product table with a
BaseNumber as entered in the BaseNumber combobox on the form? Or, if
there are, there are none with anything at all in the Extension field?
So, if this is the case, I guess we could handle it by catering to this
possibility via the use of the Nz() function, what do you think?
NextNumber = Nz(DMax("[Extension]", "Product", "[BaseNumber]='" &
Me.BASENUMBER & "'"), 0) + 1

--
Steve Schapel, Microsoft Access MVP

urr, no its just tammy spelling lol.
okay I changed that now I am getting this:
run-time error "94"
Invalid use of Null

 

[Steve Schapel]

Tammy,

No, I am not getting tired of you But you've got me stumped now! I
have tried it out myself, and it works fine for me. Just to confirm the
details of what's happening here...
- You have a table named Product, with (amongst others) two fields
named BaseNumber and Extension.
- BaseNumber is a Text data type, and Extension is a number.
- You have a form based on this table.
- There is a Combobox bound to the BaseNumber field, and the Combobox
is also named BaseNumber.
- The Row Source for the Combobox is another table that lists your
ProductFamilies.
- There is a Textbox bound to the Extension field, and the Textbox is
also named Extension.
- The code you have used is on the AfterUpdate event of the BaseNumber
combobox.
- The code goes like this...
Dim NextNumber As Integer
NextNumber = Nz(DMax("[Extension]","YourTable","[BaseNumber]=" &
Me.BaseNumber), 0) + 1
Me.Extension = NextNumber

Well, if all of the above checks out, and it's not working for you, I
don't know what's happening. There must be a simple explanation,
though, and we will crack it eventually!

 

[Guest]

Thank you Steve
Okay I went down the list and made sure I was doing everything exactly the
way you wrote it
The only thing I wasnt doing was this step:

- The Row Source for the Combobox is another table that lists your
ProductFamilies.

Now I have this set up and I am getting this error
Run-time error '3464'

Data type Mismatch in criteria expression

I checked my tables and every looks right. the basenumber is text and the
extension is a number in my product table, in my family table the basenumbe
is in text also, but I call it family.

Thank you again, I think I am getting closer

Tammy,

No, I am not getting tired of you But you've got me stumped now! I
have tried it out myself, and it works fine for me. Just to confirm the
details of what's happening here...
- You have a table named Product, with (amongst others) two fields
named BaseNumber and Extension.
- BaseNumber is a Text data type, and Extension is a number.
- You have a form based on this table.
- There is a Combobox bound to the BaseNumber field, and the Combobox
is also named BaseNumber.
- The Row Source for the Combobox is another table that lists your
ProductFamilies.
- There is a Textbox bound to the Extension field, and the Textbox is
also named Extension.
- The code you have used is on the AfterUpdate event of the BaseNumber
combobox.
- The code goes like this...
Dim NextNumber As Integer
NextNumber = Nz(DMax("[Extension]","YourTable","[BaseNumber]=" &
Me.BaseNumber), 0) + 1
Me.Extension = NextNumber

Well, if all of the above checks out, and it's not working for you, I
don't know what's happening. There must be a simple explanation,
though, and we will crack it eventually!

--
Steve Schapel, Microsoft Access MVP

Okay I tried:

NextNumber = Nz(DMax("[Extension]", "Product", "[BaseNumber]='" &
Me.BASENUMBER & "'"), 0) + 1

But extension is not incrementing by one, it just stays at 0. What I am
doing is typing a new product number into the basenumber combo box and
pressing enter, but the extension is staying at 0.

You must be getting tired of me Hopefully this will also help someone
else too.

Thanks,
Tammy

 

[Steve Schapel]

Tammy,

I must apologise. In my last message I reverted to an earlier message
to retrieve the code we have been using, and I went back too far into
history. If the BaseNumber is text, I should have said it like this...
- The code goes like this...
Dim NextNumber As Integer
NextNumber = Nz(DMax("[Extension]","YourTable","[BaseNumber]='" &
Me.BaseNumber & "'"), 0) + 1
Me.Extension = NextNumber

 

[Guest]

Hi Steve!!
Whoo hoo got it working

Now I just have to figure out how to use it . when I put it on my
existing form it isn't clearing the other fields, so I can put the new
records information in, it is keeping the very first records information.

Thank you again!!!
Tammy

Tammy,

I must apologise. In my last message I reverted to an earlier message
to retrieve the code we have been using, and I went back too far into
history. If the BaseNumber is text, I should have said it like this...
- The code goes like this...
Dim NextNumber As Integer
NextNumber = Nz(DMax("[Extension]","YourTable","[BaseNumber]='" &
Me.BaseNumber & "'"), 0) + 1
Me.Extension = NextNumber

--
Steve Schapel, Microsoft Access MVP

Thank you Steve
Okay I went down the list and made sure I was doing everything exactly the
way you wrote it
The only thing I wasnt doing was this step:


Now I have this set up and I am getting this error
Run-time error '3464'

Data type Mismatch in criteria expression

I checked my tables and every looks right. the basenumber is text and the
extension is a number in my product table, in my family table the basenumbe
is in text also, but I call it family.

Thank you again, I think I am getting closer

 

[Guest]

Now the whole thing is working!! whoo hoo, I had to change the data entry to
"no" so a new record would show.

Thank you again your awsome!!
Tammy

Hi Steve,
sorry I havent gotten back, but when I couldn't get this to work I gave up
for awhile lol.
Will this accually write the new number (BaseNumber)-(Extention) to the
table? That is what I am going to need.

You told me to put this code in the afterupdate, but do I put it in the
after update for the basenumber or the extention.
When I click on my macro (findrecord) it is still pulling up the last number
assigned, not adding 1 to it.

Thanks again for your help!!

Tammy

I can't immediately see what's wrong. Did you copy/paste directly from
the code into your newsgroup post, or did you re-type? If re-type,
kindly check that it is precisely the same in your code.

I have assumed the BaseNumber field is a number data type. If it is
text, you will need...
NextNumber = DMax("[Extension]","Products","[BaseNumber]='" &
Me.BaseNumber & "'") + 1

In my newsreader, this line of the code wordwraps to a new line after
the & but in your actual code it should all be on one line.

--
Steve Schapel, Microsoft Access MVP

Hi Steve,
I am getting a compile error, expected: list separator ), this is what I put
in

Dim NextNumber As Integer
NextNumber = DMax("[Extension]","Products","[BaseNumber]=" &
Me.BaseNumber) + 1
Me.Extension = NextNumber

 

[Guest]

I really thought I had this, I am trying to add my subform but now I have no
idea how to tie it to the new number created.
I tried this
=[BaseNumber] & "-" & [Extension]
But because its not in my table, I can't link the parent child fields.
Also I cant get EXTENSION to format to 3 places, I put 000 in the formating
but that didnt work :-(

 

[Steve Schapel]

Tammy,

To tackle these in reverse order...
Do you mean you put 000 in the Format property of the Extension textbox
in the design of the form? Well, it *should* work! Can you expand on
the meaning of "didnt work"?

Ok, and another question for you... What subform? This is the first
I've heard about any subform. The method we have been discussing is for
the purpose of creating a new record in the Product table, with an entry
of a product family in the BaseNumber field, acording to your selection
in a combobox, and the automated entry of the next sequential number in
the Extension field. Right? We are doing this on a form bound to the
Product table, right? So what's the subform, what table/query does it
relate to, what fields does it have in it, and what is its relationship
to Products?

 

[Guest]

Hi Steve,
the 000 works in the form and on the table, but when I combine the two
=[BaseNumber] & "-" & [Extension] on my main form. It doesn’t show 3
places, just one or two if the number is only 1 or 2 places, example I want
it to be 001, 012, not 1 or 12, until it reaches 3 places, then I just want 3
places I haven’t tried it in a report yet.

This one is harder to explain But I will try to see if I can explain the
whole reason for my database, it is a pretty simple database.
The number you help me create is called an ECO Number. This number is used
to keep a history of changes to engineering documents. So I have to be able
to tie this number to each document and that documents revision (my subform).
So I will have an eco number for the documents revision “Aâ€, “B†and so on
all the way to “Zâ€, sometimes past Z, depending how bad the design is lol.
I have to have this so I can create reports where when someone wants to find
an ECO for a document they can type in the document number and find all the
ecos and for that document.
Also this database helps me keep track of assigning these ECOs because each
eco must have a different number.

I also have a separate table to keep track of the different types of
documents I have, word files, excel files, PDF files and so on, so we can
find them on our massive network.

Let me know if you have any other questions
Thank you again!!
Tammy

Tammy,

To tackle these in reverse order...
Do you mean you put 000 in the Format property of the Extension textbox
in the design of the form? Well, it *should* work! Can you expand on
the meaning of "didnt work"?

Ok, and another question for you... What subform? This is the first
I've heard about any subform. The method we have been discussing is for
the purpose of creating a new record in the Product table, with an entry
of a product family in the BaseNumber field, acording to your selection
in a combobox, and the automated entry of the next sequential number in
the Extension field. Right? We are doing this on a form bound to the
Product table, right? So what's the subform, what table/query does it
relate to, what fields does it have in it, and what is its relationship
to Products?

--
Steve Schapel, Microsoft Access MVP

I really thought I had this, I am trying to add my subform but now I have no
idea how to tie it to the new number created.
I tried this
=[BaseNumber] & "-" & [Extension]
But because its not in my table, I can't link the parent child fields.
Also I cant get EXTENSION to format to 3 places, I put 000 in the formating
but that didnt work :-(

 

[Steve Schapel]

Tammy,

=[BaseNumber] & "-" & Format([Extension],"000")

So, do you mean you have a table called Products which is a table of
documents, and another table with document revisions? And the form we
have been working with so far is based on the Products table, which has
fields BaseNumber and Extension and soem other fields which you are
keeping secret? And then you have a subform, the name of which you are
keeping secret based on the table the name of which you are keeping
secret, but y'know the one with the revisions, which has some fields you
are being darned secretive about? Am I close? And then there is
something else about different types of documents, which I got lost
about there... do you mean the documents are listed in the Products
table, and also in another table as well that identifies their type, or
something, or maybe the same document can take more than one form eg
Word, PDF, etc??

 

[Guest]

Hopefully here I will show all my secrets lol.
Tables I have, I have to remember because my latest database is at work and
I am at home.

Table ECOLog (Product) This is the table I have the BaseNumber and Extension
(which should be my ECONumber used in my AffectedParts table) in, also the
following fields:
EnteredDate
ReleaseDate
DescOfChange
Status (which is tied to the Status Table)
Deviation (Yes/No)
Family (which is tied to the Family table)
ECOLocation (hyperlink to the ECO on the network)

Table Family:
FamilyNumber
FamilyDescription

Table Affectedparts:

ECONumber
DocumentNumber
Revision
Description

Table Initiator:
FirstName
LastName
PhoneExtension

Table Status:
Approved
Pending
Inactive
Obsolete

This table is pretty much a stand-alone table; it isn’t tied to any of the
other tables. I would like the affected documents to be tied to it so I don’t
have to cut and paste the descriptions
Table Documents:
DocumentNumber
DocDescription
DocRevision
DocStatus (Tied to the Status table)
DocType (this is just a note field for information)
HardCopy (yes/no field (checkbox))
PDF (yes/no field (checkbox))
WordDoc (yes/no field (checkbox))
CADFile (yes/no field (checkbox))
AI (yes/no field (checkbox))
Hex/Bin (yes/no field (checkbox))
SourceFiles (yes/no field (checkbox))
DocumentLocation (hyperlink to directory on the network)
____________________________
Okay here are my forms

AffectedDocuments (comes from the AffectedDocument table with all the same
fields, I have the ECONumber field hidden, so I only see the document number,
description and revision in my main form)
I do have this form tied to my Documents form so I can double click and
bring up my documents form so I can cut and paste the descriptions :-(

ECOLog (This is the Form used for assigning the new ECONumber)
From the Table ECOLog
BaseNumber (With the Code you gave me ) (combobox)
Extension
EnteredDate
Status (which is tied to the Status Table)
Deviation (Yes/No)
Family (which is tied to the Family table)
What I also did here is created a text box with the following
Here is your new ECO number: textbox =[BaseNumber] & "-" & [Extension] Now I
will use =[BaseNumber] & "-" & Format([Extension],"000")
I took the BaseNumber field and made it a combo box so I can see the
FamilyNumber and FamilyDescription, and hid the Extension box so the new
number would show as one number in the text box, so people wont be confused
with the two boxes.

ECOForm
This form has all the same information as the ECOLog form, except I have
ReleaseDate instead of EnteredDate and I don’t have BaseNumber or Extension
on it, just a text box with the combined =[BaseNumber] & "-" &
Format([Extension],"000")
This form also has the subform AffectedDocuments and were tied by the
ECONumber field, but now I don’t know how to tie them together.

Documents
Has all the same fields as my table.


I hope this clears up any secrets I may have, lol. I am going to now work on
the database I have here, to get it like the one I have at work. So it would
be easier to see what is happening.

Thanks again for all your help!!!
Tammy

Tammy,

=[BaseNumber] & "-" & Format([Extension],"000")

So, do you mean you have a table called Products which is a table of
documents, and another table with document revisions? And the form we
have been working with so far is based on the Products table, which has
fields BaseNumber and Extension and soem other fields which you are
keeping secret? And then you have a subform, the name of which you are
keeping secret based on the table the name of which you are keeping
secret, but y'know the one with the revisions, which has some fields you
are being darned secretive about? Am I close? And then there is
something else about different types of documents, which I got lost
about there... do you mean the documents are listed in the Products
table, and also in another table as well that identifies their type, or
something, or maybe the same document can take more than one form eg
Word, PDF, etc??

--
Steve Schapel, Microsoft Access MVP

Hi Steve,
the 000 works in the form and on the table, but when I combine the two
=[BaseNumber] & "-" & [Extension] on my main form. It doesn’t show 3
places, just one or two if the number is only 1 or 2 places, example I want
it to be 001, 012, not 1 or 12, until it reaches 3 places, then I just want 3
places I haven’t tried it in a report yet.

This one is harder to explain But I will try to see if I can explain the
whole reason for my database, it is a pretty simple database.
The number you help me create is called an ECO Number. This number is used
to keep a history of changes to engineering documents. So I have to be able
to tie this number to each document and that documents revision (my subform).
So I will have an eco number for the documents revision “Aâ€, “B†and so on
all the way to “Zâ€, sometimes past Z, depending how bad the design is lol.
I have to have this so I can create reports where when someone wants to find
an ECO for a document they can type in the document number and find all the
ecos and for that document.
Also this database helps me keep track of assigning these ECOs because each
eco must have a different number.

I also have a separate table to keep track of the different types of
documents I have, word files, excel files, PDF files and so on, so we can
find them on our massive network.

Let me know if you have any other questions
Thank you again!!
Tammy

 

[Steve Schapel]

Tammy,

Do you mean you have multiple records in AffectedParts table for any
given ECONumber? So the AffectedParts form is the subform on the ECOLog
form? If so, I would imagine you need to do one of these two things:
1. Replace the ECONumber field in the AffectedParts table with
BaseNumber and Extension fields, and use both fields as the basis of the
relationship between the two tables (and hence the form/subform).
2. Add an AutoNumber primary key field to the ECOLog table, scrap the
ECONumber field completely from the AffectedParts table, and instead put
in a Number field to serve as the foreign key for relating this to ECOLog.
I would personally prefer the second approach, but would of course
require a bit of fiddling to get the existing records to match up.

A few other random comments:
- If I understand you correctly, you should remove the Family field from
the ECOLog table, as the Family is already indicated in this table by
the BaseNumber field, so the Family is already known by reference to the
FamilyDescription field in the Family table, and it therefore redundant
to store this data yet again in another table.
- If I understand you correctly, you should remove the Revision and
Description fields from the AffectedParts table, as the Document is
already indicated in this table by the DocumentNumber field, so the
Revision and Description are already known by reference to the
DocRevision and DocDescription fields in the Documents table, and it
therefore redundant to store this data yet again in another table.
(gosh, do you notice an echo in here?)
- Not sure what all of these are, but can any given record in the
Documents table be any combination at all of HardCopy, PDF, WordDoc,
CADFile, AI, etc? For example, can a Document be ticked as both PDF and
WordDoc? If so, I won't argue with you. But if not, these should not
be separate fields.

That'll do for now

--
Steve Schapel, Microsoft Access MVP

Hopefully here I will show all my secrets lol.
Tables I have, I have to remember because my latest database is at work and
I am at home.

Table ECOLog (Product) This is the table I have the BaseNumber and Extension
(which should be my ECONumber used in my AffectedParts table) in, also the
following fields:
EnteredDate
ReleaseDate
DescOfChange
Status (which is tied to the Status Table)
Deviation (Yes/No)
Family (which is tied to the Family table)
ECOLocation (hyperlink to the ECO on the network)

Table Family:
FamilyNumber
FamilyDescription

Table Affectedparts:

ECONumber
DocumentNumber
Revision
Description

Table Initiator:
FirstName
LastName
PhoneExtension

Table Status:
Approved
Pending
Inactive
Obsolete

This table is pretty much a stand-alone table; it isn’t tied to any of the
other tables. I would like the affected documents to be tied to it so I don’t
have to cut and paste the descriptions
Table Documents:
DocumentNumber
DocDescription
DocRevision
DocStatus (Tied to the Status table)
DocType (this is just a note field for information)
HardCopy (yes/no field (checkbox))
PDF (yes/no field (checkbox))
WordDoc (yes/no field (checkbox))
CADFile (yes/no field (checkbox))
AI (yes/no field (checkbox))
Hex/Bin (yes/no field (checkbox))
SourceFiles (yes/no field (checkbox))
DocumentLocation (hyperlink to directory on the network)
____________________________
Okay here are my forms

AffectedDocuments (comes from the AffectedDocument table with all the same
fields, I have the ECONumber field hidden, so I only see the document number,
description and revision in my main form)
I do have this form tied to my Documents form so I can double click and
bring up my documents form so I can cut and paste the descriptions :-(

ECOLog (This is the Form used for assigning the new ECONumber)
From the Table ECOLog
BaseNumber (With the Code you gave me ) (combobox)
Extension
EnteredDate
Status (which is tied to the Status Table)
Deviation (Yes/No)
Family (which is tied to the Family table)
What I also did here is created a text box with the following
Here is your new ECO number: textbox =[BaseNumber] & "-" & [Extension] Now I
will use =[BaseNumber] & "-" & Format([Extension],"000")
I took the BaseNumber field and made it a combo box so I can see the
FamilyNumber and FamilyDescription, and hid the Extension box so the new
number would show as one number in the text box, so people wont be confused
with the two boxes.

ECOForm
This form has all the same information as the ECOLog form, except I have
ReleaseDate instead of EnteredDate and I don’t have BaseNumber or Extension
on it, just a text box with the combined =[BaseNumber] & "-" &
Format([Extension],"000")
This form also has the subform AffectedDocuments and were tied by the
ECONumber field, but now I don’t know how to tie them together.

Documents
Has all the same fields as my table.


I hope this clears up any secrets I may have, lol. I am going to now work on
the database I have here, to get it like the one I have at work. So it would
be easier to see what is happening.

Thanks again for all your help!!!
Tammy

 

[Guest]

Do you mean you have multiple records in AffectedParts table for any

given ECONumber? So the AffectedParts form is the subform on the ECOLog
form?<

Yes, Exactly right.

If so, I would imagine you need to do one of these two things:
1. Replace the ECONumber field in the AffectedParts table with
BaseNumber and Extension fields, and use both fields as the basis of the
relationship between the two tables (and hence the form/subform).
2. Add an AutoNumber primary key field to the ECOLog table, scrap the
ECONumber field completely from the AffectedParts table, and instead put
in a Number field to serve as the foreign key for relating this to ECOLog.
I would personally prefer the second approach, but would of course
require a bit of fiddling to get the existing records to match up.

I did number 1, it seemed alot easier the number 2
Everything is running great. It is alittle slow when I try to use the find
for the ECO Number, it will only work if I have the whole form searched for
somereason.
Now I have to figure out how to do my reports.

A few other random comments:
- If I understand you correctly, you should remove the Family field from
the ECOLog table, as the Family is already indicated in this table by
the BaseNumber field, so the Family is already known by reference to the
FamilyDescription field in the Family table, and it therefore redundant
to store this data yet again in another table.

I agree and have removed it, thanks!!

- If I understand you correctly, you should remove the Revision and
Description fields from the AffectedParts table, as the Document is
already indicated in this table by the DocumentNumber field, so the
Revision and Description are already known by reference to the
DocRevision and DocDescription fields in the Documents table, and it
therefore redundant to store this data yet again in another table.
(gosh, do you notice an echo in here?)

Boy do I know this is redundant lol, that is why I have to copy and paste
the description from the Document table to the Affected Document table. I
wish there was a way around it and will work on this after I get my reports
to work

- Not sure what all of these are, but can any given record in the
Documents table be any combination at all of HardCopy, PDF, WordDoc,
CADFile, AI, etc? For example, can a Document be ticked as both PDF and
WordDoc? If so, I won't argue with you. But if not, these should not
be separate fields.

Yes, one document can have a hardcopy and pdf, or hardcopy, pdf and word
file and so on. So I think that is the way I need to have it set up


Thank you again for all your help, this is going to be alot better for other
users to use.
Now off to figure out my query and my reports, so I can accually get
information out of the database lol.
Maybe you could help me alittle here?
Now I need to pull up the report by the ECO Number. I use to have a query
with all the information I needed for my report and I had this code in so a
pop up box would ask for the ECO number: Like "*" & [Enter ECO Number: ] & "*"
Should I be changing the stars to [Basenumber]&[Extension]? or do you have
an idea how I can do this?

Thanks again!!
Tammy

Tammy,

Do you mean you have multiple records in AffectedParts table for any
given ECONumber? So the AffectedParts form is the subform on the ECOLog
form? If so, I would imagine you need to do one of these two things:
1. Replace the ECONumber field in the AffectedParts table with
BaseNumber and Extension fields, and use both fields as the basis of the
relationship between the two tables (and hence the form/subform).
2. Add an AutoNumber primary key field to the ECOLog table, scrap the
ECONumber field completely from the AffectedParts table, and instead put
in a Number field to serve as the foreign key for relating this to ECOLog.
I would personally prefer the second approach, but would of course
require a bit of fiddling to get the existing records to match up.

A few other random comments:
- If I understand you correctly, you should remove the Family field from
the ECOLog table, as the Family is already indicated in this table by
the BaseNumber field, so the Family is already known by reference to the
FamilyDescription field in the Family table, and it therefore redundant
to store this data yet again in another table.
- If I understand you correctly, you should remove the Revision and
Description fields from the AffectedParts table, as the Document is
already indicated in this table by the DocumentNumber field, so the
Revision and Description are already known by reference to the
DocRevision and DocDescription fields in the Documents table, and it
therefore redundant to store this data yet again in another table.
(gosh, do you notice an echo in here?)
- Not sure what all of these are, but can any given record in the
Documents table be any combination at all of HardCopy, PDF, WordDoc,
CADFile, AI, etc? For example, can a Document be ticked as both PDF and
WordDoc? If so, I won't argue with you. But if not, these should not
be separate fields.

That'll do for now

--
Steve Schapel, Microsoft Access MVP

Hopefully here I will show all my secrets lol.
Tables I have, I have to remember because my latest database is at work and
I am at home.

Table ECOLog (Product) This is the table I have the BaseNumber and Extension
(which should be my ECONumber used in my AffectedParts table) in, also the
following fields:
EnteredDate
ReleaseDate
DescOfChange
Status (which is tied to the Status Table)
Deviation (Yes/No)
Family (which is tied to the Family table)
ECOLocation (hyperlink to the ECO on the network)

Table Family:
FamilyNumber
FamilyDescription

Table Affectedparts:

ECONumber
DocumentNumber
Revision
Description

Table Initiator:
FirstName
LastName
PhoneExtension

Table Status:
Approved
Pending
Inactive
Obsolete

This table is pretty much a stand-alone table; it isn’t tied to any of the
other tables. I would like the affected documents to be tied to it so I don’t
have to cut and paste the descriptions
Table Documents:
DocumentNumber
DocDescription
DocRevision
DocStatus (Tied to the Status table)
DocType (this is just a note field for information)
HardCopy (yes/no field (checkbox))
PDF (yes/no field (checkbox))
WordDoc (yes/no field (checkbox))
CADFile (yes/no field (checkbox))
AI (yes/no field (checkbox))
Hex/Bin (yes/no field (checkbox))
SourceFiles (yes/no field (checkbox))
DocumentLocation (hyperlink to directory on the network)
____________________________
Okay here are my forms

AffectedDocuments (comes from the AffectedDocument table with all the same
fields, I have the ECONumber field hidden, so I only see the document number,
description and revision in my main form)
I do have this form tied to my Documents form so I can double click and
bring up my documents form so I can cut and paste the descriptions :-(

ECOLog (This is the Form used for assigning the new ECONumber)
From the Table ECOLog
BaseNumber (With the Code you gave me ) (combobox)
Extension
EnteredDate
Status (which is tied to the Status Table)
Deviation (Yes/No)
Family (which is tied to the Family table)
What I also did here is created a text box with the following
Here is your new ECO number: textbox =[BaseNumber] & "-" & [Extension] Now I
will use =[BaseNumber] & "-" & Format([Extension],"000")
I took the BaseNumber field and made it a combo box so I can see the
FamilyNumber and FamilyDescription, and hid the Extension box so the new
number would show as one number in the text box, so people wont be confused
with the two boxes.

ECOForm
This form has all the same information as the ECOLog form, except I have
ReleaseDate instead of EnteredDate and I don’t have BaseNumber or Extension
on it, just a text box with the combined =[BaseNumber] & "-" &
Format([Extension],"000")
This form also has the subform AffectedDocuments and were tied by the
ECONumber field, but now I don’t know how to tie them together.

Documents
Has all the same fields as my table.


I hope this clears up any secrets I may have, lol. I am going to now work on
the database I have here, to get it like the one I have at work. So it would
be easier to see what is happening.

Thanks again for all your help!!!
Tammy

 

[Steve Schapel]

Tammy wrote:
....

I did number 1, it seemed alot easier the number 2

I assure you it is not!

Everything is running great. It is alittle slow when I try to use the find
for the ECO Number, it will only work if I have the whole form searched for
somereason.

I have no idea what you mean about "find", in fact I shudder to think! ;-)

....

Boy do I know this is redundant lol, that is why I have to copy and paste
the description from the Document table to the Affected Document table. I
wish there was a way around it and will work on this after I get my reports
to work

I already mentioned the way around it... remove these fields from the
AffectedParts table, and use a query to retrieve the values when needed
from the Documents table.

....

Yes, one document can have a hardcopy and pdf, or hardcopy, pdf and word
file and so on. So I think that is the way I need to have it set up
Yep.

Now I need to pull up the report by the ECO Number. I use to have a query
with all the information I needed for my report and I had this code in so a
pop up box would ask for the ECO number: Like "*" & [Enter ECO Number: ] & "*"
Should I be changing the stars to [Basenumber]&[Extension]? or do you have
an idea how I can do this?

You can make an ECO number field in the query, like...
ECONumber: [BaseNumber] & "-" & [Extension]
.... and then put your parameter prompt in this column of the query. It
seems a little weird to use a Like "*" construction in the criteria...
are you expecting the user to enter the entire ECO number at the prompt?
If so, you should just put [Enter ECO Number: ] If not, they can just
enter part of it, and what happens if they enter part of it and it
matches more than what you really want?

 

[Guest]

<I have no idea what you mean about "find", in fact I shudder to think! ;-)>

I use the find button on my ECO Form so I can find the ECO Number. Then I
enter the Documents that are affected by that ECO.

<I already mentioned the way around it... remove these fields from the
AffectedParts table, and use a query to retrieve the values when needed
from the Documents table.>

Okay I tryed what you suggested, I think I tried this before and ran into
the same problem I am having now. I can't enter the documents affected using
my form/subform.

<are you expecting the user to enter the entire ECO number at the prompt? >

Yes

Tammy wrote:
....

I did number 1, it seemed alot easier the number 2

I assure you it is not!

Everything is running great. It is alittle slow when I try to use the find
for the ECO Number, it will only work if I have the whole form searched for
somereason.

I have no idea what you mean about "find", in fact I shudder to think! ;-)

....

Boy do I know this is redundant lol, that is why I have to copy and paste
the description from the Document table to the Affected Document table. I
wish there was a way around it and will work on this after I get my reports
to work

I already mentioned the way around it... remove these fields from the
AffectedParts table, and use a query to retrieve the values when needed
from the Documents table.

....

Yes, one document can have a hardcopy and pdf, or hardcopy, pdf and word
file and so on. So I think that is the way I need to have it set up
Yep.

Now I need to pull up the report by the ECO Number. I use to have a query
with all the information I needed for my report and I had this code in so a
pop up box would ask for the ECO number: Like "*" & [Enter ECO Number: ] & "*"
Should I be changing the stars to [Basenumber]&[Extension]? or do you have
an idea how I can do this?

You can make an ECO number field in the query, like...
ECONumber: [BaseNumber] & "-" & [Extension]
.... and then put your parameter prompt in this column of the query. It
seems a little weird to use a Like "*" construction in the criteria...
are you expecting the user to enter the entire ECO number at the prompt?
If so, you should just put [Enter ECO Number: ] If not, they can just
enter part of it, and what happens if they enter part of it and it
matches more than what you really want?