Passing values to another form

[jlt199]

Hi

This is my first post to this forum, up until 2 weeks ago I had never used Access, but now I'm having to use it in "anger" at my new job and get from no-experience to at least proficient VERY quickly. So far I have designed my database, formed my Tables, set my relationships, built a number of queries and designed and made the forms I need for data entry - I'm pretty proud of what I've accomplished so quickly

My primary form is designed to enter survey data from customers. If a customer isn't already in the database, then you need to add the customer by opening another form that has all the customer info. I'm able to pass the customer ID between forms if the customer already exists, but if it's a new customer I would like to open the form, go to a new record and autofill the cutomerID text box with the number from the previous form.

I'm not at the VBA level yet, I'm currently adding functionality with the macro-builder interface thingy (image below because I think macro-builder might actual be a thing - and not what I'm using)



Please can you help me deal with passing a new customer ID to the customer info form?



Many thanks

[June7]

Are you using a combobox to select customer? Look at the combobox NotInList event. http://www.fontstuff.com/access/acctut20.htm

[jlt199]

No, I'm just using a textbox. I felt the number of customers would be too large for a combobox

[June7]

What is too large? I have comboboxes listing thousands of items.

There are several methods to pass data to another form.

1. Could try setting the DevaultValue property of textbox on the second form that references textbox on first form (seen this, never used)

2. OpenArgs argument of DoCmd.OpenForm then VBA extracts the value or maybe DefaultValue can reference to OpenArgs (never tried that).

3. VBA refers to textbox on first form and populates textbox on second form

4. with macros, use TempVars and SetValue (I don't use macros)

[jlt199]

Thanks for your reply.

How would you set up your combobox so that it's not too cumbersome to use?

[June7]

Not sure what you mean. How is a combobox more cumbersome than a textbox? Can type in combobox just as well as textbox.

[jlt199]

Ok, on your advice I have changed my textbox to combobox. When an ID not in the database is entered I am able to provide a dialog box, informing the user and asking if they would like to add a record for that ID. If they select "Yes" then I can open up a new "Customer" form and navigate to a New Record.

I still cannot autofill the ID box of this new record and I can't return to a previous valid ID in the Main form.

Any advice?

Also, I would appreciate people opinions on Macros vs. VBA. What are the advantages, disadvantages etc. and what is the general consensus among the Access community as which is the better way to go?

Many thanks

[Xipooo]

VBA is far more comprehensive and really not too difficult to learn. Macros are quite limiting because they do not give you access to all of the things you need. They provide only basic functionality.

One common practice we have is to have a table on the front end which holds specific data for that session. We call it a "WorkControl" table. On this table you can have a field which stores the Customer ID. From there you can use the "WorkControl" table with your queries to look at data in other tables, or join it with your customers table to make a new entry for that customer.

[jlt199]

Are there any potential pitfalls in using both macros and VBA in the same database?

I have have a bunch of macros, but I'm increasingly getting frustrated them. I've used VBA pretty extensively in Excel, but have had issues (different Excel versions and security settings not allowing VBA etc) distributing the VBA code to different users, which is why I was using macros this time

[jlt199]

I can now add a new record and set the value of a combobox in the same form



But, to open a different form, goto a new record and change the value of a textbox within it is still beyond me. This is what I have:



Can anyone see where I've gone wrong?

Thanks

[June7]

Did you review the link I referenced? It shows VBA approach. I don't know if macro can accomplish. I think macro uses SetValue to populate a data control.

Here's more:
http://www.blueclaw-db.com/access_no...ed_example.htm
http://www.utteraccess.com/wiki/inde...otInList_Event

[jlt199]

I wasn't sure how well a database would function with a mixture of both VBA and macros. I therefore took the decision to stick with Macros this time around.

I eventually figured it out though; using tempVars seem to work ok. I set a tempVar to the ID number entered on the Parent form and then also opened the child form from the parent form. In the child form have added a macro to the "onActivation" event. This uses the "GoToRecord" to move to a new record and the "setProperty" property to set the textbox value to the value in the tempVar.

Thanks for all you help.