Accessing Data from form to Report

[Sarah Byrne]

I Created a database that allows the user to create a jobsheet (Sign off Sheet) with inputting minimal information.I am having some problems accessing the data from my form to a report. I managed to get the full name of my AssignedTo and OpenedBy come up with this

=Trim(IIf([tblJobSheet] & " " & [tblJobSheet.AssignedTo] & " " & [Title] & " " & [FirstName] & " " & [LastName],[Title] & " " & [FirstName] & " " & [LastName]))

But this is only bringing up the information in my tblContacts and not actually assigning itself to the jobsheet. I have tried to just put in the "assigned to" but then this comes up as the ID number rather then the full name. And I have also tried to do this via the join table tblJobSheetContact but this only brings up Mr. All of which is really annoying! If you need any more info let me know.

Any help is much appreciated
Sarah

 

[Sarah Byrne]

Anyone? Still stuck with this and im all out of options.

 

[rm.harper]

Anyone? Still stuck with this and im all out of options.

Im unsure whether i can help you as Im not exactly a wizard but could you explain your problem again and ill try, just dont understand what your trying to do.

Are you just trying to collect your inputted data onto a report 'JobSheet'?

 

[Sarah Byrne]

Basically yes. I have a form that I have working and everything that goes into my report is fine but the name which I want to come up in full. I have an assigned to and an opened by field which I have made a look up for, which works fine in the form but when I preview the report it comes up as the ID rather then Mr Joe Bloggs. Its Irritating because I have tried everything. Im no expert either, I think its going to have to be VBA but I have no idea about it. So far I have done everything with out it.

Thanks for your reply

 

[rm.harper]

The way i got around this is to add the field to the report as an existing field. I had the same problem actually and managed to swindle it by adding a field instead of changing any existing ones within the form.

Ok so ill try to explain what I did and hopefully that works.
In your form where the ID(Primary key) shows, go to your 'add existing field' and drag the fields which contain the name, if youve got one field showing the full name just drag that field, if its split into first, last name add both. When your ID is present within the field it will show the id but the reference back to your source table and show the names as well.

Do you understand what im saying? I cant explain things very well haha

***If you dont wish to see the ID field then go to properties and click no to the visible option, I'm pretty sure your existing name fields will still show the address as the visible option is a design option rather than a function

 

[Sarah Byrne]

In the form it is a drop list which has an option of full names made by separate fields. Title, FirstName, LastName. with a 0;2;2;2 look up so that you cant see the ID. Which is why Im a little confused that that's the only thing that comes up. I had based it upon

=Trim(IIf([tblJobSheet] & " " & [tblJobSheet.AssignedTo] & " " & [Title] & " " & [FirstName] & " " & [LastName],[Title] & " " & [FirstName] & " " & [LastName]))

But then this only comes up as Mr. Which again is really annoying.

 

[Sarah Byrne]

Do you understand what im saying? I cant explain things very well haha

***If you dont wish to see the ID field then go to properties and click no to the visible option, I'm pretty sure your existing name fields will still show the address as the visible option is a design option rather than a function

Not particularly . the visible option just takes it away completely.

 

[rm.harper]

Not sure why it does it, just know how to skate around the problem rather than solve it

Theres only two ways ive been told how to do it. Heres a link to the solution made in my thread:

there are a couple of ways you can do this... I tend to use a combo box to 'look up' the contract and then show the related info in an unbound text box alongside...

So, as an eg:
Set up a combo box based on tblContracts, making sure you include in it the info you want to show alongside. I'm assuming you'll include ContractNo and ContractAddress (or whatever your fields are called!) Once you've done that, name the combo box... cboContracts maybe
Then, add another textbox alongside, with cboContracts.column(1) as its ControlSource
(The column numbers start at 0, so just use whatever column number you need!)

Hope that helps?

Or add the Fields; Title, First Name, Last Name as 'existing field' in the form. When the ID is shown it will show the full name.

 

[Sarah Byrne]

I have already set up a combo box for tblContacts in tblJobSheets "AssignedTo" and "OpenedBy" with this.

SELECT tblContact.ContactID, tblContact.Title, tblContact.FirstName, tblContact.LastName FROM tblContact ORDER BY tblContact.FirstName;

Why I don't understand why its not working.

I have set this up with 0;2;2;2 as the column count as well so that it doesn't show the ID. What I don't get is if I base the form upon the AssignedTo or OpenedBy how do I get the report to understand that there is more then one column the field. Its coming up with Mr which would be the 02;2;2 rather then the Mr Joe Bloggs 0;2;2;2.

Sorry if im not explaining myself well or understanding what your trying to explain. I think that's what you meant tho.

 

[Sarah Byrne]

Still stuck with this. Any one know the way forward. Its nearly the last thing to make this all complete.