Form/Subform Help Needed!

[Guest]

I posted this the other day but accidentally left out vital information so
here
we go again:

I have two tables on for a box number and one for file information.
The tables are set up like so:
tblBox
BoxNumber (Auto-Number)
PickUpDate
tblFile
FileType
FileNumber
BoxNumber
There is a one to many relationship for Box number. There are other fields,
including a primary key, in both tables that do not pertain to this.

What I need to do is set up a form that allows the user to assign files to a
box number, the file table is already populated.
My thoughts ran something like this:
In a form obtain a box number by assigning a pick-up date.
Enter into an unbound text box “file type†and “file numberâ€.
Click a button called “Add to Boxâ€.
Ideally in a datasheet at the bottom all files added to the box would be
displayed.

Basically the user will have a box of files, will get a box number and needs
to assign the
files in the box to that number. I cannot figure out how to make this work,
if it even can work. I may be way off base.

Does anyone have any suggestions?

 

[John Vinson]

What I need to do is set up a form that allows the user to assign files to a
box number, the file table is already populated.
My thoughts ran something like this:
In a form obtain a box number by assigning a pick-up date.
Enter into an unbound text box “file type” and “file number”.
Click a button called “Add to Box”.
Ideally in a datasheet at the bottom all files added to the box would be
displayed.

Sounds like you just want to run an Update query updating the selected
files Box Number. The relationship is sort of odd here: normally if
you have a relationship, you would create the box (parent) record
first, and then create new File records in a subform, inheriting the
existing box number. I take it your records now have NULL in the box
number field?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Yes there is a null in the box field. The relationship is odd basicaly I
imported all exsisting file numbers from the past 75 or so years and now need
to put them in boxes and ship them out. I want to know what box they got put
into. Any additional thoughts would be appriciated.

Jen

 

[John Vinson]

Yes there is a null in the box field. The relationship is odd basicaly I
imported all exsisting file numbers from the past 75 or so years and now need
to put them in boxes and ship them out. I want to know what box they got put
into. Any additional thoughts would be appriciated.

Do you work on them one by one, or in bunches? If one by one, could
you perhaps have a simple continuous form with a textbox or combo box
for the BoxID? This could be made pretty convenient if you put the
following code in the BoxID control's AfterUpdate event (I'll call it
cboBoxID):

Private Sub cboBoxID_AfterUpdate()
Me!cboBoxID.DefaultValue = Chr(34) & Me!cboBoxID & Chr(34)
End Sub

This will make the selected value 'sticky', so that you can select
multiple files one after another, and the BoxID will default to the
most recently entered value. (You'll need to change some other field
in the record to get the value to enter though).

Or, if you're working in defined batches, with criteria for the file
numbers, an Update query would be simpler. This will let you update a
hundred files in one click - IF you can enter criteria which specify
unambiguously *which* files.

Finally, and probably the most work: an "upside down" subform
arrangement might work. You could have the "one" side table on a
subform, with the FileID and FileType as the master/child link field.
This would require going through the files one by one though!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps