The last problem for my db! Subdatasheet issue!

[mfearer94]

Hi guys, so this is the last major issue I see possible in my db.

Right now I have my main table with a part number field and some other basic information.
I then have a table with more detailed information linked to my main table as a subdatasheet by part number. I set up a query and form for the subdatasheet.
What I need is for the user to query for the part number and be able to edit information in the subdatasheet by using the form, BUT that isn't "possible" because no information has been entered in the subdatasheet for that part number so that record really doesn't exist in that subdatasheet even though it does exist in the main table.

Does anyone know of a way that I could possibly do this?

Thanks in advance!

[TG_W]

If you are creating part numbers in a form, you could have a code to write to the subdatasheet simultaneously and create the new record there as well.

[mfearer94]

Thanks for your response and that would be great, but I'm not really that good at all with Access code so I would need somebody to help me with that.

[TG_W]

Not a problem. Can you post your database for analysis if you have not already done so? Seeing the structure will help provide further detailed help.

[mfearer94]

I'm sorry, I actually can't because the database has company sensitive information in it, but I'd be happy to answer any in depth questions.

Right now I have the main table called "Part Table" that consists of fields such as Part Number, Part Description, Quantity, Customer, Vendor, etc.
Then I created another table called "Labor" that consists of fields such as Part Number, Direct Labor Rate, Operation, Seq #, and Parts/HR.
I made the "Labor" table a subdatasheet to the "Part Table" and the master and child fields are linked by Part Number.
Then, I created a Select Query for the "Labor" table where I also included some fields for calculations and subtotals and created a relationship between both Part Numbers.
I then created a form based off of that query that lays out all of the fields.

That's really all I have done for this part of the database. I hope that's enough to help and sorry I can't post the database. Thanks for the help though.

[TG_W]

Well, you could make a copy of the database, remove the sensitive information, and post that, but I can offer up at least one suggestion without getting into it.

Create a query to identify where there are part numbers in "Part table" and not in "Labor" (join part number; include all from Part; pull down both Part Number fields; set Criteria to "Is Null" in Part number field from "Labor"). Use that query as the base to Append to the Labor table. You can have the queries launch when you load the Labor form.

EDIT:

Code to launch query on load -

DoCmd.OpenQuery "YourAppendQueryNameHere"

[mfearer94]

Wow, thank you so much! That worked like a charm, but I do have one more (hopefully) minor question.

So I put the labor form in a navigation form and I have a lot of fields which wouldn't normally be a problem, but there isn't a scroll bar and I can't figure out how to create one. This is the last question, don't worry.

[TG_W]

We all have questions from time to time - definitely not an issue.

Check your form's 'Scroll Bar' settings in the 'Format' tab of the 'Properties Sheet'

[mfearer94]

It says "Both" which I think means it should come up. The only thing I can think of is because it's in a navigation form, but I don't know if that would actually do anything.

[TG_W]

It should be coming up. I haven't really dealt with navigation forms as I haven't had a need to use them.

Digging around in a quick one I threw together, maybe change the position of the scroll bar from 'System' to 'Left' or 'Right'. If that doesn't work, start a new thread under the "Forms" section of the forum and sorry I couldn't help further.

[mfearer94]

Well I found out that there were actually two scroll bars and one of them doesn't work at all and the one that works, you can't use the scroll wheel, but it works so I'm happy. I think the reason is because its a subform, but that's just my guess. Well thanks again!