469,626 Members | 1,279 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

Copy Record Structure (Related Records)

Hello, this was particularly helpful to me as well so thank you very much.

I'm new to using Access 2003, and I have a slight twist to this question; I've been asked to make this particular button create several child records that share several of the parent's base traits.

While it's simple enough to just duplicate and loop according to the number of records the user requests, I'm unfamiliar with the steps to take from here.

The fields that DO change are my problem:

One field is an identifier. The parent's identifier would have to be changed from say, 123, to 123.1, 123.2 and so on for the children. I could use the user's input and the loop to get the number, but I confess that I really don't know how to directly modify data within the recordset using VB.

For the remaining fields that see changes, I need to I believe I need to add an input dialog for the user for each record (also in the loop presumably). I honestly have no idea what the code used here is.

I have read a number of articles on the site, but I'm sorry to say I haven't found answers to the problems above yet. If this has already been addressed, please point me in the direction. I would be most grateful for any suggestions. Thank you!
Feb 27 '08 #1
29 8561
NeoPa
32,202 Expert Mod 16PB
I've had to split this post into a separate thread as thread hijacking is not allowed on this site. As a new member it's understandable but please review the Help section at the top right for site Dos & Don'ts.

The original thread can be found at using command button to copy record in a form.
Feb 27 '08 #2
NeoPa
32,202 Expert Mod 16PB
Although I've linked the theads for you, there still doesn't seem to be an adequate explanation of your problem. Please try to explain it clearly from scratch. Without understanding the question, it's hard to give any help.
Feb 27 '08 #3
Sorry for my bad manners. I'll be more careful.

As to my garbled query above, I'll try to untangle it going one problem at a time:

I have a query is based on two tables. One (main) holds the bulk of the record data. The other is a source for a combo box, which denotes the status of each record.

The table design looks like this:

StatusID - Autonumber (key field)
Status Description - Text

The two tables have a one-to-many relationship, with the main table on the one side. The StatusID field in that table is joined with a foreign key field in the main.

The problem arises in a form I have created based on this query. There is a command button that edits the status of the record currently selected. This changes the number in the foreign ID field from one number to another.

i.e.

Expand|Select|Wrap|Line Numbers
  1. Me.Status = 3
  2.  
If this is run, the computer will display an error message. The content is as follows:

"The current field must match the join key '?' in the table that serves as the 'one' side of the 'one-to-many' relationship. Enter a record in the 'one' side table with the desired key value, and then make an entry with the desired join key in the 'many-only' table."

If more information is required, please let me know. Apologies in advance if this is so. Thank you for your patience.
Mar 3 '08 #4
NeoPa
32,202 Expert Mod 16PB
Sunshard,

Firstly, your manners are fine :) One small slip doesn't make you ill mannered. There was just a little guidance required is all. Perfectly understandable in a new member.

Moving on to your issue, could you just check your table link. You say in your post that the main table is on the one side. It seems clear to me that the main table should be on the many side (Many main records link into each one of the status records). Let's get this out of the way before we delve further. It may iron itself out naturally with this anyway.

Let us know how you get on.
Mar 3 '08 #5
Thanks NeoPa, after remedying that and removing the combo box that particular problem was resolved.

Continuing with the original problem, the command button that changes the status of the records works fine but the function is somewhat longer.

It creates a number of duplicates specified by the user, and then accepts user input to change critical fields for these 'child' records. Using InputBox (until I can figure out how to make a input form / dialog that accepts multiple inputs) I
prompt the user for information.

I suspect that a multiple input form / dialog has been discussed but I can't seem to find it. If possible, could someone direct me to such a reference? Thanks again :)
Mar 6 '08 #6
NeoPa
32,202 Expert Mod 16PB
Are you talking of an infinitely multiple form for input? Such that it would continue to provide new TextBoxes to enter data into regardless of how many are used?
Or would a fixed maximum suit your requirement well enough?

PS I'm pleased to hear that the relationship issue was resolved :)
Mar 6 '08 #7
Hello NeoPa, I am certain a fixed maximum is sufficient (say 9).
Mar 13 '08 #8
NeoPa
32,202 Expert Mod 16PB
Sorry if I'm not on subject here. I've reread the thread but can't remember my thinking when I last posted :S

Anyway, I think what you might want to do is throw up a simple form with the 9 TextBoxes on it.

You can hide TextBoxes 2 - 8 if you like and only unhide them when the previous one has a value in it.

When the "OK" button (that you will design into your form instead of "Exit" say) is pressed you can process the data back in your main form then close the data form.

Does this sound like it could work for your situation?
Mar 13 '08 #9
That sounds great actually. My only problem is my appalling knowledge of the method or syntax needed to program a form capable of doing that. I've bandied about doing that instead of using Inputboxes, but I haven't been able to figure out.

As my internet access is limited, you'll note that my response time's a bit slow. Thanks for bearing with it.
Mar 17 '08 #10
NeoPa
32,202 Expert Mod 16PB
Well, you basically follow these steps :
  1. Create a new form.
  2. Add a visible TextBox onto the form.
  3. Set the names of the label and the TextBox to something meaningful.
  4. Copy and paste the two (linked) controls to get another copy.
  5. Update names again.
  6. Set the .Visible property to False (to hide).
  7. Copy this changed one.
  8. Paste seven more times with name changes.
  9. Add a CommandButton to the form also.
  10. Come back and post when that has all been done - or ask another question related to any one of the steps outlined here.
When that's all ready we can look at adding the necessary code to match your project.
Mar 17 '08 #11
Hello, I have created the form described. Sorry for the late reply. So how would you go about doing this? :-)
Mar 28 '08 #12
NeoPa
32,202 Expert Mod 16PB
OK, well let's start with you supplying the names of the form and all the controls.

Any other relevant items will probably be required too at some point (particularly any other form involved and any controls on that form which are to be updated in any way).
Mar 28 '08 #13
The controls and their intended input is as follows:

ID Number - 'Unique' Identifier for records

Thickness |
Width |--> Number fields
Weight |

Date of Receipt |
Date of Issue |--> Date fields

Description |
Remarks |--> Text fields

The last input is a bit tricky.

Each item has a status code (an integer between 1 an 6), and I'd like to let the user decide this as well. However, for their ease of reference I'd like to present them something like an option button group. There would be three possible statuses for the items. Is this possible?

As to your question about affecting other forms / controls, the status code mentioned above is part of the relationship described earlier. If there is anything else I have missed, please let me know. Thanks!
Mar 29 '08 #14
NeoPa
32,202 Expert Mod 16PB
There seems to be some confusion here. This is not what I was asking for (No form name and no control names). A control is an object on a form or report (in this case the form controls were required). Fields are the items within a record of a table. Tables store the data, which may be accessed and/or displayed via forms or reports. Queries are dynamically created datasets extracted from the original data in the tables. Queries also have fields.

While thinking this through again, I've decided it may actually be easier to approach this from another direction anyway, in which case the field names (that you did provide) may come in useful anyway.

Correct me if I'm confused, but you want to create multiple records from data where you know most of the intended fields, but one (at least) of the fields is not known for each record? Would that be a fair description of the situation?
Mar 31 '08 #15
*slaps forehead* sorry about that. Okay, you are mostly correct. The fields I have described are the ones that either change or the user needs to input (so mostly is probably not quite right). The other fields remain unchanged, and can simply be copied.

The fields I have described are all present in the form of text boxes. The control names are the same as the field names given above with all spaces replaces with _. (i.e. Date_Of_Receipt)

The form itself is called Create Child Record.

Once again, sorry for the poor phrasing and please let me know if further information is needed. Thanks!
Apr 1 '08 #16
NeoPa
32,202 Expert Mod 16PB
What was your thinking on my second and third paragraph? I feel this would be a more sensible approach to take with my limited understanding of your situation.

For this (as well as the other approach) I will also need to know which fields are propagated, and which need to be entered / selected by the operator.
Apr 1 '08 #17
Understood, no problem. I'll note the fields that will simply be copied from the original record:

The following fields are text type: Manufacturer, Manufacturer Number, Manufacturer Description and Manufacturer Remarks

The following field is date/time type: Modification Date

The fields mentioned in my post above are to be edited by the user.

Thanks for the prompt reply, is there anything else you need?
Apr 2 '08 #18
NeoPa
32,202 Expert Mod 16PB
Yes. I need you to answer the question in the first paragraph (of post #17). This is the same question as that asked in paragraphs #2 & #3 in post #15.

Can I ask you please to pay closer attention to what you're asked in future. I'm happy to answer technical questions but most of what's gone on so far is my asking, and re-asking, for simple information.
Apr 2 '08 #19
*sigh* I'm sorry about my poor communications. I'll try to actually answer you. If once again I completely miss the point, please just leave this thread be until you look at this without getting frustrated by my rambling. I can't really offer more than profuse apologies. ;_;

Your question:

Correct me if I'm confused, but you want to create multiple records from data where you know most of the intended fields, but one (at least) of the fields is not known for each record? Would that be a fair description of the situation?
This is correct, and to answer this:

For this (as well as the other approach) I will also need to know which fields are propagated, and which need to be entered / selected by the operator.
In post #18, the fields listed there are propagated directly from the record to be copied. The fields entered/selected by the operator are in post #14.
Apr 3 '08 #20
NeoPa
32,202 Expert Mod 16PB
Right, as we know what we need to achieve, the other approach (which I will outline shortly) appears to me to be more appropriate for the situation.

The approach I'm talking of is to have a subform for the new records. The main form contains the parent record and the subform contains the child records. Each child that you enter is added when you fill in the details in the subform. The fields that are the same as in the parent can be automatically set for the child by changing the .default property of the fields to reflect the values in the parent record.

Does this sound like it could be a reasonable solution for you?
Apr 3 '08 #21
NeoPa
32,202 Expert Mod 16PB
Just to throw a spanner (wrench) in the works :

It's actually quite bad practice to store the same data in two places in a relational database as you seem to be intending.

If a parent has a field [A] that is common to all its children, then the data should be stored in the field [A] of the parent and the children don't need a field [A]. The children are able to link into the parent using a query (SQL) and thereby reference the associated value.

The most obvious problem with storing values more than once is that of maintaining consistency across the database. This is described and explained better than I can in Normalisation and Table structures.

PS. This is for your consideration. I'm not about to refuse further help simply because you may be going about things the wrong way.
Apr 3 '08 #22
Thanks for pointing this out NeoPa, I understand the importance of storing the same data more than once. In this particular case, it might be harder to do.

The products involved can easily be reclassified and there are a very large number of unique attributes available for each field that could be normalised. On top of this, these values are unlikely to be repeated very often as a lot of the products are tailored to suit the customer's situation.

I apologise if this situation or my description of it further confounds your efforts to help. If possible, I would like to hear your advice about creating the form. However, I have taken your advice to heart and will consult with the users who have in-depth knowledge of the product to attempt normalisation. Thanks once again.
Apr 8 '08 #23
NeoPa
32,202 Expert Mod 16PB
...
If possible, I would like to hear your advice about creating the form.
...
You will find my latest advice in post #21. I'm awaiting a response to this before knowing which direction to proceed in.
Apr 8 '08 #24
That approach sounds great, how would you go about it?
Apr 10 '08 #25
NeoPa
32,202 Expert Mod 16PB
Firstly you need to use the wizard to create the form with the subform.

When that's done let us know and we can look at tidying up including some code to set the defaults of the subform from the main form.
Apr 10 '08 #26
Alright, I've created the subform inside the form. The subform is currently unbound. I'm ready to link the fields together.
Apr 13 '08 #27
NeoPa
32,202 Expert Mod 16PB
I'm sorry, but the subform needs to be bound too.

We are not trying to redo Access here. Rather, we want to make use of what Access already provides with a view to just tweaking the bits that are not automatic.

A bound subform means that when we add a record the linked parent/child fields are created correctly automatically. Only those fields that are not handled automatically need to be handled in code. We never have to code up a Save (or copy or whatever else) routine for this. It's mostly automatic and ordinary - which is good news.
Apr 14 '08 #28
Okay, sorry for the delay in reply; someone stole the cabling for our office.

I assume I bind the sub-form to the main table as a data source? And set the form open mode to add? I admit I'm a little distracted by the repair job at the moment, so if something I just wrote is off please let me know.
Apr 15 '08 #29
NeoPa
32,202 Expert Mod 16PB
OK.

Please excuse my terminology if I go off track. It's hard to keep up with threads sometimes if they go on a long time with many posts (otherwise I can just reread to catch up, but this one's a little convoluted).

Anyway, if my memory serves me correctly you are looking to add records to table [b] which match many of the details of a selected record in table [A] (which I think is your master table).

If this is true then you need a form (bound to table [A]) which contains a subform (bound to table [b]). The linked fields should reflect the index of table [A] that logically links the two sets of records together (usually the PK of table [A] and an FK of table [b]).

To give an overview now of our thinking, we will allow Access to ensure that the FK in the [b] records match that of the selected [A] record, then we will set the default values of the controls that aren't managed that way, and that you want to be defaulted, to the values taken from the selected [A] record.

I hope this makes sense and gives you a clearer idea of what to do next.
Apr 15 '08 #30

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Sean Howard | last post: by
19 posts views Thread by davidgordon | last post: by
reply views Thread by igendreau | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.