Recent content by NotAnExpert

Thank you for this. I will give it a try now.

Hi, thank you for coming back Gasman. I appreciate the input. This is one of those cases where 'you don't know what you don't know'. I had considered finding the ID of the new record in Subform2 but in my case, the record to update from Subform3 should always be the last record. This is the...

Hi, I have a mainform, and the mainform has 3 subforms on it. For the sake of this exercise I will call them Subform1, Subform2 and Subform3. Part 1 On the mainform, when I type an ordernumber into the search box, a query shows records with items on that order on Subform1. This works perfectly...

I think it might be best if I try to explain a little better. As i'm not sure the references look right to what I need... I have a main form and that mainform contains two separate subforms. Both subforms are linked to the mainform by an ID. The first subform gets data from SQL Server table...

Hi Pat, thanks for asking. The original data sits on SQL Server, but I need to be able to duplicate very specific information from this database into Access for an entirely separate purpose. With regards to record duplication, yes, certain records will have multiple entries against it i.e...

Hi all. I have a parent form with two sub-forms on it. The first sub-form is filtered by selection on parent form. However what I want to do is double click the record in the first sub-form which will then populate an entry in the second sub-form. If I double click again on the first sub-form I...

As always, excellent responses, thank you very much!

Hi, having sorted my previous issue with nz, I am now stuck trying a similar approach to dates. I have my mainform, and a subform is updated with a button onclick event. My mainform contains two textboxes which are formatted for dates and has datepickers etc. My query runs ok using NZ but as...

Thank you all for your responses, finally got it working. Now I need to have the subform use this instead of the standard query when it loads up :)

I have just tried the code and it worked, however I have another question... Nz([Recnum], " & Me.txtRecNo & ") LIKE '*" & Me.txtRecNo & "*' I'm not sure if I understand this right but it looks like the NULL replacement is whatever is in the textbox on the mainform?

Out of curiosity, why would this work from the WHERE clause and not the initial SELECT part?

Because the example code I posted above has a loop in it that a standard query wont allow for.

I appreciate that, I have tried just using a query in query design and this works perfectly, essentially replacing null values with an empty string, which stops the row from being ignored, but doing this in VBA and passing that as the query through that way is the ideal solution for me here. I...

Thanks for that one, i've tried that but it still didn't include the rows where the value is null. I'm not sure what to try next...

Hi, i'm struggling with using Nz with SQL in VBA, I believe it's the use of " that might be the issue. Would one of you kind fellows be helpful enough to show me where I might be going wrong? The idea is that there is some data in a single table which isn't relational, but the user wants to be...