Add new record in web database with macro

[George-Bowyer]

I am just getting started with web databases in Access 2010, my previous experience being with Access 97.

I am trying to divide my customers up by (user-specified) regions.

I have a table (tblCustReg) with a record ID field, fldCustomerID and fldRegionID fields.

I have a form with a combobox and two subforms (child1 and child2).

The user selects a region from the combobox and the datasheet in child2 filters to show the customers allocated to that region (the datasheet is built on a query that combines tblCustReg with tblCustomers). This works.

I can double click a customer in child2 to delete a record in tblCustReg.

What I want to do is be able to double click a customer record in child1 to add a new record to tblCustReg.

I hope that makes sense.

Can anyone please suggest a method to approach this?

Thank you.

 

[George-Bowyer]

Ok, I've been busy.

I've discovered Table Data Macros and I have set up a TDMacro on tblCustReg that is called by double clicking on child1, supplying the parameters to the TDM to make the new record. That all works. Hurrah!

The last thing I need to do is add something to the end of the double click macro on child1 to requery child 2.

Can't get that to work, yet...,

 

[George-Bowyer]

Doh. I just use the same macro that is used to set the filter on child2 when the user first selected the region.

Sorted.

(Although, I do sort of get the feeling that I'm talking to myself, here... )

 

[nto]

Well, you haven't been talking to yourself entirely... How were you able to get the data macro to add a new record? I'd like to do the same, but found that "The CreateRecord data block can only be used in the After Insert, After Update, and After Update data macro events." (msdn.microsoft.com/en-us/library/office/ff835671.aspx)

So... how do you add a new record BEFORE an insert or update event? Thanks!

 

[Uncle Gizmo]

I too am watching with interest. I don't think many people here use macros and I would guess that not many use Web Access either.

My understanding is that "web databases" are Controlled by table macros only, no VBA? So I don't foresee me having much to do with them.

The other thing I have noticed, by reading people's posts about web databases is that the table macros seem to have limited functionality compared to MS Access in general. Again that's another reason I can't get excited about them!

 

[nto]

Yeah, unfortunately using a 2010 Access web database is a necessary evil for me at this point since every year users will be changing and I can't install clients on each desktop...

 

[George-Bowyer]

Ooo. Here's a blast from the past.

I'll start by saying that I gave up on web-based access databases because I just couldn't get the functionality that I needed without VBA. I now use a web-based backend that holds the tables in sharepoint (hosted in office365) and vba-heavy front ends that sit on the end-users own PCs.

The downside of that, of course, is that each user has to have Access2010, or better, to be able to run the front-end. However, with office365, that's no longer that expensive (although it is still a cost).

(When the user installs Office365, I then use TeamViewer to remotely connect to their PC and set everything up for them)

But, anyway, getting back to the problem (and trying to cast my mind back 2 years)...

Can you give me more info about what you are trying to do and how you are going about it?

Are you specifically creating a Table Data Macro (ie, one that you create from the relevant tab in the table tools tab of the table's toolbar) and not an "ordinary" macro created under the "Create" tab?

I used a "named macro" Table Data Macro, which I then called from the button on the form.

 

[nto]

The problem is pretty simple (or so it seems). I have a multi-item subform. On the parent form I have a button that says "Add." When I click the button, I want a new record added to the subform that the user can then edit.

 

[George-Bowyer]

Sorry, I lost sight of this. Have you resolved it yet?

 

[nto]

Thanks for checking back! I should have posted sooner. I ended up creating a small subform (let's call it subfrmAdd) with fields for the information I wanted to add (Name, date, etc.) to the multiple-item subform (subfrmList). The record source for both of the subforms is the same. On the parent form, I created an "add" button which I set via macro to requery both subfrmAdd and subfrmList, effectively saving the record, clearing subformAdd, and updating subfrmList. Kind of messy, but it works!

 

[George-Bowyer]

Good. Sorry I wasn't more help.