Copying Data from Text to Numbered Ref Field

[Novice1]

I have a table that has a field [UnitID], which is a number field, that is relatedto a table [UnitID], that lists various unit information (UnitName, UnitPOC, UnitPhone, etc.). On my form the unit field is bound by UnitID but displays the name of the unit.

Periodically I import an Excel spreadsheet (individuals, units, etc.) into another table (Potential Customer List), which lists people and assigned units (text field).

When I add a new customer I open the potential customer list, then click a button to transfer the customer information from the potential customer list into another form, no problem except one field, the unit field. I believe this is because the field is a text field in the potential customer list but a number field in the form I'm transferring to. I've tried many ways to capture the data from the Potential Customer List.

Forms!frmPCSTracker.UnitID = Forms!frmAlphaRoster.Unit 'doesn't work

and other copy techniques.

 

[JHB]

You could use the CLng function.

 

[Novice1]

Doesn't the CLng function change the value to an integer? I don't believe this will work because the number be arbitrary.

If the field on the Access form (bound to a number) displays text; and the user enters text, isn't there a way to capture/copy data, from another form, into the form?

 

[Gasman]

Doesn't the CLng function change the value to an integer? I don't believe this will work because the number be arbitrary.

If the field on the Access form (bound to a number) displays text; and the user enters text, isn't there a way to capture/copy data, from another form, into the form?

PMFJI,

Use the VAL function then?

http://www.techonthenet.com/access/functions/numeric/val.php

 

[jdraw]

Novice1,

Please give readers a sample with some typical data so we can see your issue/need. How is UNIT formatted?

Also doesn't work doesn't convey much info to the reader. Perhaps you could again show us using some sample data what this means.

 

[Novice1]

Maybe my explanation is bad.

In the primary form, I have UnitID, a number field (used to look up unit information in another table ... (UnitID, UnitName, UnitPhone, UnitPOC, etc.).

In my secondary form, I have a text field. The UnitName matches the unit name in the look up table above. I can cut and paste (CtlC / CtlV) without a problem but using VBA I cannot. I believe it's the text field vs. number field formats.

 

[Novice1]

Error on this line: Forms!frmPCSTracker.UnitID = VarZ

Runtime Error 440: The value you entered isn't valid for this field.


Dim VarX As Variant
VarX = Forms!frmAlphaRoster.AAC

Dim VarY As Variant
VarY = Forms!frmAlphaRoster.ALC

Dim VarZ As Variant
VarZ = Forms!frmAlphaRoster.Unit


DoCmd.GoToRecord , , acNewRec

Forms!frmPCSTracker.RName = Forms!frmAlphaRoster.FullName
Forms!frmPCSTracker.Grade = Forms!frmAlphaRoster.Grade
Forms!frmPCSTracker.SSN = Forms!frmAlphaRoster.SSN
Forms!frmPCSTracker.DOS = Forms!frmAlphaRoster.DOS
Forms!frmPCSTracker.SubjectEMail = Forms!frmAlphaRoster.OfficialEmail
Forms!frmPCSTracker.UnitID = VarZ

 

[Novice1]

I continue to struggle with this. Any suggestions or "work arounds?"

 

[JHB]

I continue to struggle with this. Any suggestions or "work arounds?"

Did you read post #5.

 

[Novice1]

Yes. I posted code in #7

 

[JHB]

Yes. I posted code in #7

No not really, you didn't show any sample data!
Like:
123
564
1A2B
etc.

 

[Novice1]

Sorry ...

Primary Table (tblPCSTracker) has a field UnitID (number), which has a one-to-many relationship to a unit table which stores unit information.

The Primary Table (UnitID) is combo box which is bound to UnitID but displays only the unit name (col width 0";1"). So, the number is stored in the table (1, 2, 3) but displays the unit name (23 EMS, 23 AMXS, etc.)

I have another form (Excel data imported from another source) that has the unit name (23 EMS, 23 AMXS) but not the associated (UnitID).

I want to copy unit name, which matches the data in the primary table, but I cannot.

 

[jdraw]

It sounds like you are using Lookups at the table field level.

It is also evident you are dealing with a form.

I don't understand this

The Primary Table (UnitID) is combo box which is bound to UnitID but displays only the unit name (col width 0";1"). So, the number is stored in the table (1, 2, 3) but displays the unit name (23 EMS, 23 AMXS, etc.)

Can you show us a jpg of your tables and relationships?

 

[Novice1]

Pics 1 and 2 show design of UnitID in the primary table. While bound to col1 (number), the unit name appears to user (Pic 3), col2.

Excel spreadsheet data is imported into a table (Pic 4). The name of the unit is imported (text data).

I would like to copy the info from Pic 4 to the Pic 1 table but the field in the primary table is a number, I cannot get the clear text (Pic 4) to copy

 

[Novice1]

Any help would be appreciated.

 

[JHB]

You could use the DLookup to get the UnitId associated to the Unittext.
Change the field- and table-name(s) to what you use

Forms!frmPCSTracker.UnitID=DLookup("[[B][COLOR=Red]UnitId[/COLOR][/B]]", "[[B][COLOR=Red]tblUnit[/COLOR][/B]]", "[[B][COLOR=Red]UnitText[/COLOR][/B]]='" & Forms!frmAlphaRoster.Unit & "'")

If you can't get it then post some sample database.

 

[Novice1]

Attached is a snapshot of the relationships. Any help would be appreciated.