Do.Cmd OpenForm vs Form.Recordsource = SQLstring ??

[liamfitz]

I'm wondering why my MainForm is not allowing me to Add records ( using the navigation button provided. It's greyed out, and not doing anything at 'Add records' ) Howver, my 'Subform' is. The only difference I can see, is that my Mainform uses a 'DoCmd.OpenForm' method, to set the recordsource, whereas my 'Subfrom' uses a Form.Recordsource = SQlstring.
Here are the actual code commands ...

DoCmd.OpenForm "frmAfAIS(test)", , qrySelectClientsByAdvocate, _& "Staff_ID=" & sid
DoCmd.Requery
for the MainForm, and ...

refSQL = "SELECT tblReferrals.Referral_ID FROM tblReferrals WHERE Client_ID=" & cl & " AND Staff_ID=" & sid & ";"
Me.sfrmRefs.Form.RecordSource = refSQL
sfrmRefs.Requery
for the Subform. Any help would be greatly appreciated.

 

[RainLover]

Why Requery? You have just opened so that is not needed.

Does the Form do what it should if opened from the Database Window.

 

[liamfitz]

I'll remove th Requery. Not sure what you mean by opening the form in the " database window " ( If you mean opening it from the navigation pane - NO it still doesn't allow additions ) Thanks.

 

[RainLover]

Yes Nav pane.

I did not know what version you are using.

Does your properties allow for new records?

Also I suggest for testing you set the Recordsource to the query you mentioned in the first post.

You can tidy up later once the primary problem is solved.

 

[liamfitz]

Yes it Does, and the 'AllowAdditions' property for the mainform is set to Yes ( along with all the other other record editing properties ) I've had an idea. Use the existing 'Query' I base my Mainform on to create ( using the template wizard form ) a new form. Ill try to work it out from there, depending on whether this new form allows me to add records or not, how they differ ... (I'll forget the Subform for now ) thanks.

 

[liamfitz]

P.S. I'm running Access 2010 on Windows Vista.

 

[RainLover]

Try setting Data Entry to "No"

 

[liamfitz]

No it doesn't. It also prompts me for a variable.

 

[liamfitz]

I've discovered the difficulty. It's the keyword 'DISTINCT' in my query. I use it because it is pulling off a recordset of Clients, who have a 'case'. However, because any given Client can have more than one 'case' ( and do ), it creates 'repeat' records of the Client details ( which I only require one of ) as many times as there are 'Cases'. I'm rethinking the field, I use to filter the data. I'll let you know if I resolve it.

 

[RainLover]

Number of Records?

Look at the Join between the Tables. Is that corrrect?

 

[liamfitz]

Not that easy ... My Mainform does not display just fields from ONE table ( in this case CLIENTS being the 'main' table ) there are several tables linked by P.K. in their respective tables, indicating personal details relating to clients, in an obviously "1:Many" relationship ( i.e MANY Clients can share the same/or 'one' Ethnicity, Religion etc. ). Is this what you are asking about, as the possible hindrance to it allowing adding new records ?

 

[liamfitz]

Many thanks for your advice, but I appear to have 'cracked it' now ( mainly by trial and error ), and am creating simpler Recordsets dynamically( in Form_Load events ), using values in queries set as Recordsources, to ensure I get the right records in the way I need them. It's all alearning process, but I'm getting there.