Macro to Enter data from userForm into unlinked table (1 Viewer)

[mgav]

My db has tblBrokerage, tblHedgeB. They share 9 columns. (Edit - I understand that duplicate data is terribel design however it is a client request for reference when completing the different user forms) tblBrokerage has a Form where users will enter data for tbl_Brokerage. It has a button I've been building a macro for to send data for the shared columns from the Form to the shared colulmns in tblHedgeB.

I imagine the correct steps to be:
1. create a local variable equal to the value of the txtBox containing the first shared column (Bnum)
2. open the table tblHedgeB
3. go to a new record
4. enter the variable value into the column for Bnum

I believe I have steps 1-3 completed but cant figure out step 4.
The steps I build are also just for 1 shared column, not 9, not sure how to approach that yet.

 

[pbaldy]

Why not use a bound form? You're making things very difficult for yourself.

 

[plog]

2 huge red flags--using a macro and calling fields 'columns'. Not picking on you, but those are signs of inexperience. Everyone has to start some where and maybe you aren't that familiar with access, so..

I have to ask--why are you not just using a bound form?

Introduction to forms - Microsoft Support

Learn about the different types of forms in Access databases and how to create them.

support.microsoft.com

It seems you are trying to reinvent the wheel because you don't know how simple wheels are to use.

 

[plog]

Found a 3rd even huger red flag on a reread

My db has tblBrokerage, tblHedgeB. They share 9 columns.

That's not how databases are to work. You don't duplicate data all over the place. Instead you relate tables and once they are in a relationship you can use queries to combine them to get all the data you need together.

I fear you have not set up your tables properly. That process is called normalization:

Database normalization - Wikipedia

en.wikipedia.org

Before you start working on forms you need to set up your tables and fields correctly. Give the link above a read, work on some tutorials and then apply what you learn to your database.

 

[mgav]

Why not use a bound form? You're making things very difficult for yourself.

Its bound to a different table, though I didnt even think to check about linking it too

 

[pbaldy]

Its bound to a different table, though I didnt even think to check about linking it too

Then Plog's excellent thoughts about normalization come into play. Perhaps if you gave us an overview of your table structure someone could offer some better alternatives.

 

[mgav]

 

[plog]

Put aside forms for now. We need to focus on your tables and fields. Probably the best way is for you to upload a sample database. You can strip out all the data and upload if it has sensitive data.

Or you can complete the Relationship Tool in your database, expand all tables so we can see every field, take a screenshot and post that pic back here.

 

[Gasman]

O/P states they are not able to respond to this thread.

Error responding in thread: "Oops! We ran into some problems."

I posted a question and when attempting to respond, I get error code "Oops! We ran into some problems. If it keeps happening contact an administrator". It happens when I don't include any attachments or links. What can I do?

www.access-programmers.co.uk

 

[Pat Hartman]

New user, not enough posts to include attachments.

From what I can tell by the original post, you seem to be trying to store the same data in multiple tables. That isn't the way relational databases work. Each attribute is stored in one place. You use queries to join tables together so you can select columns from each table and work with them as a set.

Try using a bound form to work with the data. Access makes this pretty easy. You can start with a wizard that uses either a table or a query as its RecordSource. This binds the form to that table/query. Then you can pick columns from the list of available fields and Access will build a form. It will have bound controls. When you change the value of a control, Access will eventually save that into the table. You can force an immediate save by pressing the save icon in the Ribbon. Or, you can navigate to a new record or close the form. Whenever you do something using the interface that makes the form move to a different record, Access will ALWAYS save the record you just dirtied. Once you get the hang of how a bound form works, you will get to the point where your question becomes -- How can I STOP Access from saving a record Then we'll talk about various form events and where you need to put code to control how the form works.

You need to get to 10 posts before the site will allow you to post attachments. So, stick with us and see how we can do this using just our words

 

[Gasman]

Pat, O/P said

It happens when I don't include any attachments or links.

 

[Pat Hartman]

We can tell by his post count that he cannot include any attachments or links.

 

[Gasman]

We can tell by his post count that he cannot include any attachments or links.

Yes, but they have tried without either?
I was going to suggest clearing cache, but has they have tried two other browsers, that is not going to help.