Show Query Result in Form Fields

[Burty]

i'm a begginer at access, so please excuse me if this question is very basic.
i have a multiple table select query which shows me which user has which piece of a equipment at that given time.
I also have a form that logs any problems the users have with the equipment, what i want that form to do is show the results of the query based on inputting the users name. ie put the serial number and model number into the relevent fileds on the form.
I can get the form to run the query but that just shows it in the query window, i need it to show on the correct feilds on the form.

Hope that makes sense and that somebody can help me.

Cheers

Burty

 

[FoFa]

If there are mulitple rows per user, then use a subform and link it by the person to your main form.
If you only have one row, or wish to show one row, populate the form fields using dlookup or something like that. But from the sounds of it a subform is what you need, and pretty much what it was designed for.

 

[Burty]

Thanks for your help on that.
However I've tried using the Dlookup because i only want to show one row, this is how i've done it:-

=DLookUp("[SerialNumber]","currentequipqry","[Usercode]=" & [Usercode])

However it just diplay's error in the serial number feild.

Is this because the table that the form is based on is not part of the query? Or doesthat not matter?
The query contains TblUserDetails, TblEquipmentInfo, and the joining table is TblHistory which shows the user along with the serialnumber of the equipment they have along with a 2 dates issued and returned.
The query shows basically the user, some equipment detail, and when it was issued and the return date is set to Is Null, it is sorted by issue date so that the most recent issue is at the top.
the form is actually linked to another table call tblLogcall, but 3 fields from that form user, model type and serial number are what i need to get populated by the query.

I'm sure i'm missing something basic!!!

Thanks

Burty

 

[FoFa]

Is Usercode numeric? If it is text then you need to change to:
DLookUp("[SerialNumber]","currentequipqry","[Usercode]='" & [Usercode] & "'")
(basically, surround Usercode with single quotes)
ALSO where is UserCode coming from? From a form? Then it shold be something like Me.UserCode or Forms!MyFormName!UserCode

 

[Burty]

I've changed it to

=DLookUp("[SerialNumber]","currentequipqry","[Usercode]='" & [forms!frmLogCall!Usercode] & '''')

The error i get is #Name? and it's telling me it's a circular reference.

The user code is text and is in both the query and the form, however from 2 different tables usercode in the query is from TblUserInformation and the usercode on the form is from TblLogCall, could that be what the problem is??

 

[FoFa]

Sounds like you have multiple columns the query thinks is called UserCode, I would alias those in your query, in Query Designed it would like:
Usercode1: TblUserInformation.Usercode
Usercode2: TblLogCall.Usercode

Then you can reference either Usercode1 or Usercode2.
However if both of those contain the same value, I would just not show one of them in your Query and leave it called Usercode.

 

[Burty]

I'm not quite sure how to alias, i tried going into properties on the table in the query but it said something like the alias is too long, so i figured i was doing it in the wrong area.
Usercode in each table would have the same values, the only difference would be that userinformation would have all the users and log call would only have those that have logged a problem.
Can i just check that the Dlookup expression goes in the control source of the serialnumber field on the form if that is where i want it displayed??

Thanks again

 

[FoFa]

If the USERSOURCE for both table contains the same information, only include ONE on your query output. See if that fixes your problem.
I meant Alias a column, not a table. You go through properties to alias a table, you alias a column in the QBE grid. If you want to see this in action, go to an empty column in the query grid, enter "TESTING" (with the double quotes) in the top box (where the column normally goes). Then tab, click in a currently used column and you will see it change to expr1: "TESTING". That expr1: is the column alias.

 

[Burty]

Hi

I have been putting the dlookup in the control source of the serial number is that why i have been getting the circular reference?? If i move it for example to the lost focus function that circular ref disappears. However it does return another problem which is "the object doesn't conatin the automation object 'Forms!FrmLogCall!engineercode'
the code i have typed in is
=DLookUp("[SerialNumber]","[currentequipqry]","[Engcode]='" & [Forms!Engineercode] & "'")
I may be going way off track but i don't seem to be getting anywhere.

You may have noticed that i have changed the user code to engcode and engineer code. Eng code being from the table in the query and engineercode from the form, with the hope that may have made it easier to differentiate!!

Thank you for your patience

 

[Burty]

I've managed to sort that out thanks for your help!!