Referencing fields in a Form

[ravnwing]

Hello.

I have a database in Microsoft Access which I need to update with new fields occasionally, and some of the fields are based on others. What I want to do is fill in the fields that I am given, and then use a "Complete Record" button to fill in the absent information. My question is how does one use VBA to access the fields that are written into text boxes on my Add Entry Form?

[twinnyfo]

ravnwing,

I think I know what you are trying to do, but it is not very clear. What have your tried so far, and what types of errors or inconsistencies are your facing?

Without seeing some examples of what is not working, it is difficult for us to guide you toward problem resolution.

[ravnwing]

I haven't really tried much. at the moment, I'm not trying to do anything complicated, I know the logic once I can add or edit data, but I dont know how to do that so I cant go very far. Right now I just went with a test line of code to see if it worked, based on something read somewhere else.

Form(recordForms).field3.text = 25

It didnt work. recordForms is the name of teh record, and field3 is the name of the text box, which normally pulls data in automatically from the database. I thought to just move to a new record in the form, fill in the fields I could, and then I want to write a program to fill in the rest.

[twinnyfo]

I'm still confused.

What do you mean "write a program to fill in the rest"? The rest from where? From Post #1, how is the DB to "complete the record"?

I'll do my best to walk through a solution with you, but your descriptions are not very helpful. I understand I was a novice at one time too, so some of the language may not come naturally. What would help is for you to describe in detail waht you want the db to do. Even if it is painful to describe in such detail, it would be more helpful to us, as I think you feel absolutely stuck because you know what you want it to do, but don't know how to describe it.

Keep at it andwe will work through this.

[ravnwing]

okay. let me start again.
I have an access database that, with the help of people here, I wrote a program for that takes in information from a text file, and extrapolates other pieces of information. It has 30 fields in total, including the id number which is just a primary key. Most of the time, I can continue to import from text files to add records, but sometimes I have to input data by hand from physical documents, and these physical documents do not have all the information I need; it still needs the data that is extrapolated from my other program.

So, the first 10 fields are provided me, details of items, inventory numbers, prices and quantities. The next three are conditional, fields that exist for some items but are far more often left blank
The next 7 fields are just cost totals: prices times quantities. The logic behind that is very simple, a single line of code, and it will be done. But I don't know how to reference the right fields so that the code can access the quantities and prices.
The next 7 fields are more complicated. Based on an identifying field (what will be a select case function in the code) I then multiply the total cost by a certain percent, and record that percent cost to be totaled later. I know that I can do that, because I did for the code that imports the data from a text file. I just don't know how to pull the right data.
And the last field is just at total of the percentage costs. again, easy code.

The form that I am using is basic. It is basically just a reorganization of what comes up when you are looking at a database and select create form. What I intend to do is go down to the bottom where one can navigate from record to record and press the "new record button", the arrow with a star over it. With that I will have a new record. I want to fill in the first ten fields, and then click a button that I have made at the bottom of the form that stats a VBA program, running code that fills in the rest of the fields.

Does that make any more sense?

[twinnyfo]

Bear with me, because I "think" I understand, but let me know if this is correct:

A: You import data from a text file. This text file has everything you need to create a record. It also calculates certain fields, based on information found in the text file and all that data is saved to a Table, record by record.

B: Sometimes, you have to manually input records (containing like data), but, your Form does not calculate those particular field that it should. You want your form to calculate those missing fields and save that data to the table.

Is this correct?

If so, here is generatlly what you want to do:

1. When you create your Form, use the Table described. Create a text box for each field you need in the Table (I assume you have done this already. Be sure to use a good naming convention for your Text boxes, such as txtItemID, txtInventoryNumber, txtPrice, txtQuantity etc. I say this, because long-term, when you are building databases in the future, it will be helpful. make sure that the Control Source for each Text Box is proerly set.

2. When you manually enter data into your Form, you want your form to calculate other fields, yes? So, since you can't calculate the total cost of Price x Quantity, you will have to wait until you have both of those values before you calculate. Let's say that the price is entered first, then the quantity. If this is the case, for your txtQuantity text box, in the After Update property, click on the dropdown for Event Procedure and then click the three dots, which will take you to your VBA editor. Then, add some code similar to this:

Private Sub txtQuantity_AfterUpdate()
    Me.txtTotal = Me.txtPrice * Me.txtQuantity
End Sub

This will automatically update your Total Field (and update the Table). Perform similar actiohns for all of your calculated Fields.

Or, if you have a series of fields that need to be calculated at the end, you could assign all those updates to a Command Button using VBA.

Either way, this should work pretty easily, once you get the hang of it. Play around with this for a while and let me know if you run across any problems. We'll try to work through it with you.

Hope this hepps!

[ravnwing]

That was exactly what I needed. Thank you so much.

[twinnyfo]

Great! Keep me posted if you need anything else!