VBA Add New Record if none found

[nj2la96]

Hi Guys,

New to the forum. I need help on some vba coding for a db I'm developing.

The Setup: There is a clients table, a property table and a jobs table. Each client can have multiple properties and there can be multiple jobs done on each property.

The form: I created a form that uses a drop down to select the client, once the client is selected the subform shows the properties for that client. I can navigate through the properties to select any of the required properties and on this same subform, there is a button to open the jobs related to the property selected.

The problem: The form and subform work fine. The last form (the button that opens up the jobs) works ONLY IF there are records available. If there are no jobs for that property, it doesn't allow me to enter a new record. I'm pretty sure that the issue is because the jobs form has a query record source that uses all three tables linked (I'm pulling the clients name from the clients table and the property name from property table).

What I need is some vba coding to check if the recordset is empty and if it is then add this entry. Also, I'm not sure if this should add this code to the button or on the on_load of the jobs form.

Any help would be appreciated.

 

[CJ_London]

Sounds like you need to simplify the jobs subform recordsource - you shouldn't need to bring through data from the other tables.

So something like

Main form recordsource=tbClients

in the main form, have an unbound hidden control (we'll call it lnkProperty). In the controlsource put

=[propertysubformcontrolname].[form].[propertyid]

change names to suit

property subform recordsource=tblProperties - link child and master on clientID

jobs subform recordsource=tblJobs - link child as propertyID to master as lnkProperty

 

[nj2la96]

I apologise, I don't think I follow you completely.

1. have an unbound hidden control (we'll call it lnkProperty)...do you mean like have a disabled text field with these properties?
2. property subform recordsource=tblProperties - link child and master on clientID..if the recordsource is tblProperties then linking would change this to a select query as the record source, is this what you mean?

 

[CJ_London]

1. have an unbound hidden control (we'll call it lnkProperty)...do you mean like have a disabled text field with these properties?

Not sure what you mean by disabled, but create a new textbox and set its visible property to No and in it's control source put the code I suggested

2. property subform recordsource=tblProperties - link child and master on clientID..if the recordsource is tblProperties then linking would change this to a select query as the record source, is this what you mean?

No - the recordsource for the subform is just tblProperties (or whatever you call this table). In the subform control set the LinkChild property to ClientID (or whatever you call it in tblProperties table) and the LinkMaster property to ClientID or whatever you call it in tblClients (or whatever you call this table)

 

[nj2la96]

thanks I will try this out now. The easy fix I found was using nested subforms so at least there is an option. Thanks I will keep you posted.

 

[CJ_London]

Sounds like I slightly misunderstood - I thought you had a main form with two subforms, one for properties and one for jobs. Not a subform within a subform.

 

[nj2la96]

oh no you misunderstood. It was a main form containing a drop down for clients, then in this form is a subform containing the properties. Within the subform properties is a button which OPENS (not a subform) a new window containing the third subform for the jobs. My issue was that entering new records in the subform (properties) works and updates the table correctly but the popup form (jobs) wasn't. I hope this clears it.

 

[CJ_London]

OK, in that case ignore what I was suggesting! and try this instead

1. In your popup form just have the name of your jobs table as the recordsource.
2. When you open the form (using the button on the property subform) add a WHERE to the openform command

docmd.OpenForm "tblJobs",,,"[PropertyID]=" & me.PropertyID

If you want to pass info through - to populate the caption for example - use the Openargs parameter e.g.

docmd.OpenForm "tblJobs",,,"[PropertyID]=" & me.PropertyID,,,me.PropertyName

and then in the open event of your popup form put:

me.caption=me.openargs

 

[nj2la96]

that did the trick. Thanks a million. *thumbs up*