Macro to Enter data from userForm into unlinked table

[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

 

[Gasman]

Pat, O/P said

It happens when I don't 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.