Unbound fields and queries

[limperger]

Dear all:

I would like your opinion on this: is VBA code used for linking unbound fields with queries (the so called recordsets???). In other words, is there a way to insert data in a form from a data source different than the form's own data source, without using list or combo boxes?

I'd like your answer only, not how should it be done. That is, for example, "yes, there is a way to insert that data in a form without listboxes, but VBA is mandatory" or "yes there is a way without using listboxes and even withou using VBA". It is only to know what I should I learn next.

Best regards

[Stewart Ross]

Limperger, you ask a question then bound it with 'don't tell me how it should be done'??? This is a public forum, and perhaps others who read this thread may wish to know how it should or could be done, even if you don't.

You have also not been particularly clear in explaining the background of your question, which makes it difficult to 'read the runes' over what you are really asking here.

In answer to your question, at least as far as I can tell what it is: VBA is not the only way to update data from unbound controls - Access queries in SQL can be used as well. However, the Access database engine has limitations which mean that in some circumstances references to controls on forms (bound or unbound) will cause queries to fail, in which case VBA solutions may be the only practicable ones.

-Stewart

[limperger]

Hello Stewart:

First and foremost, thank you for your response.

Let me explain myself: when I enter this forum, I feel very humble. My Access ' knowledge is similar to that which may be learnt using an "Access Bible" book, with no knowledge of VBA or SQL. In this forum, topics beyond my Access ' knowledge are dealt and I have the impression that I have posted topics that are far easier to deal with than the average knowledge discussed in here (and I apologize for that). Nevertheless, I have found very useful insights and I thank all of you for that.

This time, I just wanted to know if unbound fields in a form can be populated with data whose source is different from that of the form. I felt VBA should be used here (as you have pointed out) but I was not sure. I just wanted your thoughts on this because I feel my next step in my Access learning process is to, precisely, learn VBA. But what I didn't want was to make you waste your precious time explaining how to proceed with VBA code when I have no idea of it.

And last but not least, I would like to apologize myself for my bad explanations offered in here. Maybe it is due to my limited English skills (though I'm studying to improve them).

Best regards

[limperger]

Maybe if I paste here the SQL statement, it will help:

[PHP]SELECT [CALCULO IPI].[IPI CALCULAT]
FROM [CALCULO IPI]
WHERE ((([CALCULO IPI].HISTORIA)=[Forms]![Registro_frm]![HISTORIA])))[/PHP]

"Calculo IPI" is a query that makes a calculation which is displayed in the "IPI calculat" field. With the specified criteria, it only makes the calculation for the selected record in the "Registro_frm" form ([HISTORIA] serves as the primary key). When the form is open and a record is selected, then if the query is launched, only a result is displayed (the "IPI calculat" for the selected record). Then, I would like this result to be displayed in an unbound field in the form "Registro_frm", without using a combo/list box. Is it possible? Maybe using the DLookUp function? Or VBA?

Thank you very much in advance.

Best regards

[limperger]

Hello!

Could someone have a look on this?

Best regards!

[NeoPa]

I was going to reply earlier, but I had a busy day yesterday and couldn't get on.

On that point, could I suggest that an appropriate interval to wait before bumping a thread should be 24 hours at least. We try to get things sorted faster than that, but there are so many reasons (principle of which is that all members are volunteers) why we cannot guarantee any timescales.

As far as your question is concerned, I'm a little confused as I thought you had made it clear that all you wanted was a simple indication of whether or not VBA coding would be requiired. As Stewart answered this clearly in his post I'm not sure where we're at and what you are looking for further.

[NeoPa]

Maybe if I paste here the SQL statement, it will help:

[PHP]SELECT [CALCULO IPI].[IPI CALCULAT]
FROM [CALCULO IPI]
WHERE ((([CALCULO IPI].HISTORIA)=[Forms]![Registro_frm]![HISTORIA])))[/PHP]

"Calculo IPI" is a query that makes a calculation which is displayed in the "IPI calculat" field. With the specified criteria, it only makes the calculation for the selected record in the "Registro_frm" form ([HISTORIA] serves as the primary key). When the form is open and a record is selected, then if the query is launched, only a result is displayed (the "IPI calculat" for the selected record). Then, I would like this result to be displayed in an unbound field in the form "Registro_frm", without using a combo/list box. Is it possible? Maybe using the DLookUp function? Or VBA?

Thank you very much in advance.

Best regards

Maybe it will help if I simply treat this as a new question unrelated to your first post. Let's see what we can do.

In this case I would add some code into your Form_Current() event procedure similar to the following (assuming your unbound control is also called [IPI CALCULAT]) :

Private Sub Form_Current()
  Me.[IPI CALCULAT] = DLookup(Expr:="[IPI CALCULAT]", _
                              Domain:="[CALCULO IPI]", _
                              Criteria:="[HISTORIA]='" & Me.[HISTORIA] & "'")
End Sub

If [HISTORIA] is numeric rather than string, then replace line #4 with :

Criteria:="[HISTORIA]=" & Me.[HISTORIA])

Good luck with this :)

[limperger]

Dear NeoPa:

Thank you for your response (as always).

Best regards

[NeoPa]

I'm pleased I could help :)