Multiple records in one form display

[Don]

Hi,

I have a form for property data with a table record source that
contains all the pertinent data to the property i.e. size and physical
characteristics. Within that form I have a subform that looks like a
list to record bids received for that property. The subform's record
source has a one-to-one relationship with the main form's record source
so that the bids received are unique to the property in the main form.

My subform's table has unique records which include the same
SaleUnitID. Each record has a field for Bid1Date, Bid1Name, Bid1Amount,
Bid2Date, Bid2Name, Bid2Amount and so on up to ten. I thought this
would okay but now I'm having trouble querying the data by bidder's
name. In some records they are Bid1Name and in others they are
Bid2Name. Also, the amounts are not directly linked to any specific
bidder.

I figured that I better replace that table with one that has a unique
record for each bid and its data. I figure that I can create a
one-to-many relationship with the main table by still using the
SaleUnitID.

However, I'm unable to figure out how to replace the subform. I would
like it to look like a list of bids for the user but I don't know how
to allow multiple record entries from one form display.

 

[Don]

Sorry, I figured it out. Continuous form.

 

[strive4peace]

Hi Don,

The first thing to do is create a new related table for bid info and
then transfer your data using APPEND queries

*SaleBids*
SaleBidID, autonumber
SaleUnitID, long integer -- FK
BidDate, date
Amount, currency
BidName, text (if this is first and last name, you should separate them)

there is no need to re-enter the data ... that is what APPEND and UPDATE
queries are for:

if you follow these directions, you should have it done in under an hour

1. define the SaleBids table as described above

3. make an APPEND query based on your current bid table
Append to SaleBids
(Query, Append ... from the menu)

field --> nz(SaleUnitID,0)
Append To --> SaleUnitID
Criteria --> <>0
Criteria line 2 --> <>0
Criteria line 3 --> <>0

field --> BidDate1
Append To --> BidDate
Criteria --> is not null
(just so you don't get empty records

field --> nz(BidAmount1,0)
Append To --> Amount
Criteria --> <> 0
(put on second criteria line so it makes an OR condition)

field --> Bid1Name
Append To --> BidName
Criteria --> is not null
(put on third criteria line so it makes an OR condition)
-- not sure about this one in the field cell -- was kinda confused by
your explanation about which name field related to the bid set

Run!

now, drop down the list in each field cell and change fieldname to #2

also change

field --> BName: IIF(len(trim(Bid2Name,""))>0, Bid2Name, Bid1Name)
Append To --> BidName
Criteria --> is not null
(put on third criteria line so it makes an OR condition)
-- not sure about this one in the field cell -- was kinda confused by
your explanation about which name field related to the bid set

Run!

change field again to the next number, run,...

and so on until you have created a record for each set of Bids (up to 10
since that is what you said you have)

now, delete the relationship (link line) between wherever SaleUnitID is
defined and SaleBids on SaleUnitID -- make sure to enforce referntial
integrity

Ideally, BidNames would be in a separate table and you would just store
ID's...

~~~~~~~~~~~~~~~~
Make a BidNames table

First make a main table for your names

make a query based on SaleBids

change it to a make-table query
from the menu --> Query, Make-Table --> BidNames

change it to a Group/Totals Query
from the menu --> View, Totals

field --> BidName
Totals --> Group By
Criteria --> is not null

Run!

If BidNames is actually a first and last name, you will want to parse
this info into their own fields

Once the BidNames table is created, modify the structure to add -->
BidNameID, autonumber

In your SaleBids table, add the following field:
BidNameID, long integer

~~~~~~~~~~~~~~~~~~
QUERY TO UPDATE RELATED ID

you are storing a text field in a related table that relates to a text
field in the main table -- add a long integer ID in the related table --
and want to populate it with the IDs from the main table (

make a new query based on the table you want to change, SaleBids

add the main table (BidNames)to your query

link the two tables on the common text field (BidNameID)

change the query type from a Select Query to an Update Query
from the menu bar --> Query, Update

on the grid:

field --> BidNameID
table --> SaleBids
UpdateTo --> BidNames.BidNameID

then, RUN (!) your query

a quick way check to ensure that all related IDs were filled out
(without writing comparison query) is:
1. open related table, SaleBids
2. sort by common text field, BidName
3. visually scan ID field, BidNameID, to make sure it is filled out for
every record where there was text

Naturally, if you have thousands of records, you can design a query to
show records where text Is Not Null and ID_field Is Null

-- then, add those unmatched text values to the main table
OR edit them so text text matches and run query again and/or manually
fill IDs

Then, when you are satisfied that all data has been linked, delete the
unnecessary text fields from the tables you have replaced ID in, and
compact/repair database to regain the space it was using

naturally, back up your database before running any action queries on it

~~~~~~~~

whew! once you have done THIS, post back and we will help you with your form

Hopefully, if there are errors, you will see the logic and be able to
correct them


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

Hi Don,

glad you got it!

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*