Solved: VBA table population

[jludt]

Hello,

This is my first post and I am relatively new to VBA but not to programming in general.I have a small table that I successfully created a small search function for and now I am trying to find a way to add records to the table.

As of right now I have a main form with 2 buttons, one for the search and one for the add. The form for the add is finished except for the VBA in the back end. The fields in the add form correspond to the fields in the table but I can't figure out how to go about getting new records into the table. Is it an SQL INSERT statement? Or is there a built in function in VBA that will allow me to do this?

I was hoping to get some insight on this as well as maybe an example. Anything else I can do to clarify my problem, code, screens etc. let me know I would be glad to post. Thank you ahead of time.

[CreganTur]

Welcome to the forum- always good to see new members.

Access can handle adding new records natively- just make sure that the form's record source is an editable query or the table itself. It sounds like the addition form is only for adding new records- if this is true, then you can make its record source be the table and set it's Data Entry property to Yes (on the Data tab of the property sheet). This will set the form so that it will not show any existing records- it only allows records to be added to the table.

HTH:thumb

[jludt]

Cregan,

Thank you for your quick response I appreciate that greatly. I got sidetracked and ended working on something else. I wasn't exactly clear on your response though. Are you sayign taht I don't even need VBA to accomplish this? I looked at the properties of the form and didn't see anything saying "Data Entry" in the Data tab. I was hoping you could be more specific for me. This is basically my first time doing anything like this in Access. Thanks man.

[CreganTur]

Are you sayign taht I don't even need VBA to accomplish this?
That's exactly what I'm saying. As long as your form is tied into an updateable record source, then any changes made on the form that are saved (generally by moving to the next record) will be automatically written to the backend data source.


I looked at the properties of the form and didn't see anything saying "Data Entry" in the Data tab. I was hoping you could be more specific for me.

Look at your form in design view. Make sure the little box in the upper left hand corner of the form's window has a little black box in it- that means the form object is selected and the properties you see on the properties sheet are for the form itself, not one of its child objects. Look at the Data tab and you will see a property named "Data Entry"- generally it is sixth from the bottom. Set it to True.

Let me know if you need any more help:thumb

[jludt]

Ok that worked nicely. Thank you. That was really simple. What I had actually done was copy an existing form that added data to a table and tried to modify the VBA behind it. Turns out I was making more work for myself than there had to be.

One more question though, I now have a form that populates the table correctly except now every time that form appears it is filled with an existing table entry from a previous query. I just want them to be blank. I'm sure this is probably just as simple but I'm struggling. I do greatly appreciate your help thus far though.

[CreganTur]

I now have a form that populates the table correctly except now every time that form appears it is filled with an existing table entry from a previous query. I just want them to be blank.

If you set the Data Entry property to true, then you won't see any existing records- it only allows new records to be added.

[jludt]

Ok I got you. My problem was I couldn't' find the DataEntry property because I keptl looking at the text box properties not the form properties. Took my a few minutes but after a quick Google of DataEntry Properties I got a screen shot and knew I wasn't looking at the right properties screen. Thanks again. I know this was really simple and I appreciate your patience.

Sorry for the edit. But I have one more thing that is bothering me before I consider finish. For some reason when you open the form to add new data the second field takes the focus. I tried adding this VBA to


Private Sub Form_Current()

Name.SetFocus

End Sub



to the forms On Current event. I am getting an "Invalid Qualifier" error. Any ideas as to why? Or how else I can go about accomplishing setting the first field to take focus instead of the second?

[jludt]

nm, lol.

i just fixed it with by telling it what form the text box name was in like this..


Private Sub Form_Current()

Forms!FrmSeneca_Add!Name.SetFocus

End Sub


Last question I swear. Thanks again.

[CreganTur]

DOn't feel bad for asking questions- it's how I learned.

You can also determine which item gets focus first by arranging your tab order. This is what allows you to set in which order objects get focus when the user hits the tab key- it's always a good idea to set this up, otherwise you could have users randomly jumping between objects with no real rhyme or reason. Whichever items is first in the list is the one that will start with focus.