Concatenating or joining fields?

[twosides]

I have a main table that is called 'Floor' which has several fields in it RefID being one of these. Also there is FlrID. One is for the actual building you are in the other will be for the floor level you are on as you do the site survey.
I have another table called Block and another Level.
The block table has two field names in it thus RefID and Blk. (This is actually a block or building on a site)
RefID is a primary key and is used as a unique number 1,2,3...6 so on while the actual Blk has the actual name or letter given to the building ie Main Hall, B blk, Main Entrance ....and so on.
The Level table is similar to the Block table in that it has a FlrID and Flr.
One being primary and unique and the Flr being the floor name ie Ground, Basement etc etc.
The RefID in the floor table is then linked to the RefID in the Block table. Same for the Level table. I then use a combo box to lookup the data but ask it to display the 2nd column data rather than the first which is obviously the RefID value, using the 'Column Count' setting. This looks up the data in the linked table but then displays the adjacent (2nd) column.

I use at the minute a field on the form (at this stage I am not writing the value to the table just getting it to display on the form) to concatenate the Block and Level values of each separate table. Hence, i put this in my 'Control Source' for the textbox on my form: =[Blk] & [Lev]

Its hard for me to explain without a diagram but here goes:
What I want is for the field on the Main form to display the concatenated results of the 2nd column in the 'Block' linked table and the 2nd column in the 'Level' linked table. At present it is only displaying the 1st column values.

Ultimately I will want it to write the concatenated data to a table field but I need to think about that further as it will be doing it every time I open a record which will be excessive and slow things down. But for now I just need the correct columns concatenated.

Cheers.

 

[DCrake]

Simple Software Solutions

When concatenating fields form a form use

Me.Results = Me.FirstField & " " & Me.SecondField

 

[twosides]

Thanks for the reply.
Will this still be able to be input into the 'Control Source' for the text field?
Cheers

 

[DCrake]

Simple Software Solutions

If your underlying recordsource is a query why don't you join the two fields together in your query, such as.

FullName:[First Name] & " " & [Last Name]

Then in you form use the field FullName as the control source

CodeMaster:

 

[twosides]

Cheers for reply again.
I have no queries in the database as yet.
One form and one main table titled 'Floor' and two or three other tables as previously mentioned that are linked to provide a lookup feature for combo box function.
Really am struggling this end with it.
Cheers

 

[DCrake]

Simple Software Solutions

Ok Then,

Go to queries and create a new query
Add the Floors table
Drag and drop the fields form the table down to the middle pane to add them to the query output.
Go to a blank column and create a new field as described in earlier post.

Save the query as QryFloors

Design the form
Then in the recordsource property of the form use the drop down list to select QryFloors query

Then in the form control that you want to display the concatenated fields choose the one you set up in the query.

This should work ok.

David

 

[twosides]

Cheers David,
I have done this I really now looking back I have no idea why I did not do this in the first place.
The only thing is I have dragged across all the fields and then made a form from them but now not all record display when a click through them on the form. I have set no criteria and have yet to create the concatenating field either.
Why would this be?
Cheers.

 

[twosides]

Your help above has solved the issue. I can actually do what I need to re the joining of this data visually.
The problem of not all records being displayed was due to the join method ie did not allow for blank fields.

What is the best way of writing the joined or concatenated information back to a field in a table?
The reason I ask is will it slow the performance of the form down if it is writing the data each time a record is navigated to or past?
My guess is that it may but surely there must be a cleaver little way of actually saying; look at value in the two fields before the concatenation process, if the same as the destination then do nothing else update.
Having said that, this code will also consume time to run so which is the best option?
Cheers