Combo Box/DLookup/Subform - Please help!!

[Guest]

I have a combo box on one form which pulls the company name and masterID # (autonumber) from the master table for choices in the combo box. The masterID # is then stored on a work table. Then I have a query for the Master table to match up the masterid # in the work table with the information in the master table. I do this by querying the master table and placing a DLookup for the work table masterID # in the MasterID field of the Master table.

This works just fine and I'm able to pull up the next form, which is my ad sales form. The ad sales form only has the company name and master ID # on the top section, with a subform below for the multiple sales

All the existing sales that are assigned to that company are listed. The linking field (MasterID) is working just fine for that. However, when I try to enter new data in the subform, the MasterID # doesn't hold. The error message is "The Object doesn't contain the automation object "Master"

I'm suspecting it has something to do with the fact that I'm doing the DLookup on the autonumber? I've checked and everything appears to link okay as far as viewing, but it's somehow within the editing process

I had this system in another database, however, I had the DLookup on the Company name, not the MasterID autonumber. It caused problems in this database as in this database some companies are listed more than once and if I link it in the combo box by company name - it will only pull up the first record for that company name in the second form. I solved that problem by having it link in the combo box by the master ID #, but now I have this problem about editing

Any suggestions would be extremely appreciated!!!

 

[PC Datasheet]

Teri,

You don't need the DLookup at all and you don't need the Work table! You need
the following tables:
TblMaster
MasterID
CompanyName

TblSale
SaleID
MasterID
SaleTo
SaleDate

TblSaleItem
SaleItemID
SaleID
Quantity
Desc
Price
Discount

You need a form/subform to enter sales where the main form is based on TblSale
and the subform is based on TblSaleItem. In your main form you need your
combobox based on TblMaster where the valuue of the combobox is set as MasterID.
The LinkMaster and LinkChild properties of the subform control need to be set as
SaleID.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

I have a combo box on one form which pulls the company name and masterID #

(autonumber) from the master table for choices in the combo box. The masterID
# is then stored on a work table. Then I have a query for the Master table to
match up the masterid # in the work table with the information in the master
table. I do this by querying the master table and placing a DLookup for the
work table masterID # in the MasterID field of the Master table.

This works just fine and I'm able to pull up the next form, which is my ad

sales form. The ad sales form only has the company name and master ID # on the
top section, with a subform below for the multiple sales.

All the existing sales that are assigned to that company are listed. The

linking field (MasterID) is working just fine for that. However, when I try to
enter new data in the subform, the MasterID # doesn't hold. The error message
is "The Object doesn't contain the automation object "Master".

I'm suspecting it has something to do with the fact that I'm doing the DLookup

on the autonumber? I've checked and everything appears to link okay as far as
viewing, but it's somehow within the editing process.

I had this system in another database, however, I had the DLookup on the

Company name, not the MasterID autonumber. It caused problems in this database
as in this database some companies are listed more than once and if I link it in
the combo box by company name - it will only pull up the first record for that
company name in the second form. I solved that problem by having it link in the
combo box by the master ID #, but now I have this problem about editing.

 

[Guest]

There's one thing I forgot to mention which is the combo box is not based on all the records in tbl Master, but rather on only a portion. I have a query that pulls the records from that table that I want to appear in the combo box.

So I have the combo box set to show the company name and the MasterID from that query I made in the combo box.

With what you said, I tried to do it, but still coming up short

Thanks for bearing with me - I'm not incredibly adept at this. I'd really love to solve this soon. It's probably something so simple that I'm missing.

Ter

Please bea

 

[PC Datasheet]

If the list of companies in the combobox is what you want, the combobox is
correct.

Explain what you mean by 'but still coming up short' and will try to help you
further.

Steve
PC Datasheet

There's one thing I forgot to mention which is the combo box is not based on

all the records in tbl Master, but rather on only a portion. I have a query
that pulls the records from that table that I want to appear in the combo box.

So I have the combo box set to show the company name and the MasterID from

that query I made in the combo box.

With what you said, I tried to do it, but still coming up short.

Thanks for bearing with me - I'm not incredibly adept at this. I'd really

love to solve this soon. It's probably something so simple that I'm missing.

 

[Guest]

Thanks Steve for helping me

Well, this is what is happening now. I have the combo box pulling from the query that pulls up the record that I want (linked to MasterID as you said). When that appears in the combo box and I proceed to the next form, which is Ad Sales - the company name and MasterID # appear at the top of that form as it should

Whereas the way I had it before with putting it up on a work table, I could view all the previous sales records in the subform of Ad Sales, but couldn't add new ones. Now nothing appears in the subform, and I can't add records either - so it's like the link isn't working. Although I have both the child/master property both set to MasterID.

Any thoughts? Here's my email addess if you'd like to respond that way - thickerson(remove this before mailing)@insightbb.co

Ter
Right now, Ad Sales and Master are linked via MasterID.

 

[Guest]

I now have everything working except one thing. All the records are there on the 2nd form (Ad Sales) with the subform Ad Sales. But, when I go to add a new record, it loses the link - the MasterID doesn't hold

This is probably something pretty simple but is giving me fits

Any help is most appreciated

Ter

 

[PC Datasheet]

Teri,

Compact your database, zip it and send it to me and I will be glad to take a
look at it.

Steve
PC Datasheet
(e-mail address removed)

I now have everything working except one thing. All the records are there on

the 2nd form (Ad Sales) with the subform Ad Sales. But, when I go to add a new
record, it loses the link - the MasterID doesn't hold.