I need to create buttons that add e

[dimmech]

I need to create buttons that add entries to a subform's table.

i.e. click "left message" to add record to "calls": & "LM" to Subject:

ProspectsForm:tbl "prospects":
ProspectID:
Subform:"call list":tbl "calls":
ProspectID:
CallID:
Date:
Time:
Subject:

Also would like to know how I can "move(deleting prospect record&quotthe entered information from prospects to an order form by say, clicking a "sale" button.

Thanks for help or suggestions.

 

[bhoran]

More than one way to skin a cat here.

One way is to link the table and subform at each control, i.e. each text box or combo/listbox etc on the form would have the source property set to the relevant field in the table. When the user enters the detail in the for it automatically updates the table when they move to the next control and the total record will be update when moving onto the next record. This way you only need a button to goto a new record. You can also set the subforms property to data entry Yes. Thereby the user will click on your button (can make with the wizard) to get to a fresh clear line to start entering again.

The second question you seem to want to add the record to a new table then delete the old record??

If so you need at least 2 querys one to append the record to the sales table then the other to delete it from the prospects table. You can then setup a macro which will run the append query then the delete query. I would also suggest another step where you check the record exists in the sales table before and after append using a conditional macro and a dlookup function. If the record exists on the sales table already you don't want to append it again. And you don't want to delete it form the prospects table if it did not append correctly.

You may also wicsh to check that the record has been deleted corectly before moving on.

So I expect your Macro to go something like this:

DCount( &quot;[uniqueField]&quot;, &quot;tblSales&quot;,&quot;[uniqueField]= Forms!frmRecordOnform&quot <>0
... Msgbox &quot;Record already exists&quot;
... stop macro
openquery &quot;Append to sales
DCount( &quot;[uniqueField]&quot;, &quot;tblSales&quot;,&quot;[uniqueField]= Forms!frmRecordOnform&quot = 0 or is null
...MsgBox &quot;Record not Appended, please see database administrator&quot;
... stop macro
openquery &quot;delete from prospect&quot;
DCount( &quot;[uniqueField]&quot;, &quot;tblPropects&quot;,&quot;[uniqueField]= Forms!frmRecordOnform&quot <>0
...&quot;record not deleted please see DB administrator&quot;
...stop macro
msgBox &quot;Update completed correctly&quot;

This way you will be sure that process is working correctly.

Then create the button and assign the macro to the OnClick event.

I hope that is helpful.

 

[dimmech]

I got a late start on this but your pointers are right on target. Still working it into my app as time allows. Just wanted to say thanks. Ill post in this thread if i run into any snags, is that alright.

 

[bhoran]

Dimmech

I hope you got it working fine.

 

[dimmech]

I'm in the process of starting a company and most of my time is consumed. I'm not in the position to hire someone to do this or believe me I would. That leaves yours truly to "attempt" it when I can. I have a couple of days now to see if I can get this going so any assistance given will be received with sincere gratitude.

I have created append and delete queries and the macro to run the queries as suggested. My situation is that the 2 tables "prospects" and "customers" must be compared using multiple fields.

Example(here 3 fields must match before record exists flag)

If john doe(tblrospects)works for bogus inc. and buys (then append to tbl:customers. If jane doe at bogus inc. exists in tbl:customers, ask, then), create new record for john or cancel.

Also, in the case where two names(different people) are the same and there is no companyname to differentiate the two, I would like to use the zip code/city to resolve it.

Purpose:
I would like to move a prospect into a costomer db with whatever information is available about the prospect. The available information may populate different fields with the exception of first/last name. If a match is found based on 2 or more fields the user should be able to view the records and decide to append or cancel.

All comments or suggestions are welcome.

 

[tracy1234]

dimmech - in the previous discussion, it seems as if you are deleting records from the prospect table after they are appended to the customers table. If this is so, then why would you need to test to see if the customer already exists before you do the append? Is there a chance the customer will end up back in the prospects table after they are converted to a customer?

-Tracy

 

[dimmech]

Yes, the prospect lead is generated in a manner that they may be an individual within an organization or the organization itself. It is likely that an organization will have multiple prospects and it is also likely that the individual will be a prospect outside of the organization. In both instances the customer may end up in the prospect table.

 

[tracy1234]

How about if instead of deleting the records from the prospect table, you added a customer_id when the prospect is converted to a customer?

This would keep you from re-adding the already converted prospects to the customer table.


-Tracy

 

[dimmech]

I am not sure what you mean.

tblrospects
keyrospects_id(auto number)
fields:firstname, lastname, company, city...

tbl:customers
key:customers_id(auto number)
fields:firstname, lastname, company, city...

what am I not seeing.

DCount( "[firstname]", "customers","[firstname]= Forms!frmRecordOnform") <>0
... Msgbox "Record already exists"
... stop macro

If john exists in customers the macro stops here.

 

[tracy1234]

dimmech - I was just suggesting an alternative which would be to add the foreign key customer_id to the prospects table to help you to identify prospects that were already turned into customers.

If you are happy with the direction that you got from bhoran then you should go with that. If you just want to continue with what bhoran has provided, then you should probably post specific questions about that method.

-Tracy

 

[dimmech]

Thank you for your comments Tracy. I think it's great that people like you take their time out to try and help people like me in this way.

I believe that what is needed for this project is a search function based on multiple fields in the record exists check that tells me if a record is similar to another with the option to proceed adding the record anyway or canceling.

Saying it is easy, doing it is well, you know...

I'll be checking to see if anyone picks up on this til I have the chance to work on it again. If not, I'll reword and repost.

Thanks again,
dimmech