Solved replicationid used as an primary key and a foreign key how to get it to open a form

[UltimateNeo]

Hello,

I have a table with an ID field and a text field the user enters the text and i want to find the id for that text in my table stampnoT

text field is stampno

In another table called stampholdert i have the ID as a foreign key called stampnoid this is bound the id field

i want to open the form displaying the record in the stampholdert

at the moment i get ????????? instead of the replicationID value to use

code stampID = Nz(DLookup("ID", "StampNoT", "StampNo='" & UCase(StampSearch) & "'"), "")
DoCmd.OpenForm "NewuserF", , , "StampNoID={" & stampID & "}"

NewuserF is a cotinuous form and has the StampNoID field

Stampsearch is a textbox where the user enters the stampno they want to find

Thank you for any help

 

[MajP]

Is a replication id a string with curly brackets? If so it needs single quotes around it.
My guess it is just a number and the brackets are only formatting and do no really exist. Get rid of brackets.

 

[CJ_London]

The replicationID is a GUID and needs to be treated as such. It does not make a good PK as it takes 16 bytes rather that 4 for an autonumber so ultimately will be slower performing. Why are you using a replicationID?

 

[UltimateNeo]

I got it working using recordsets, the reason i used a replicationID was because you can have infinite records whereas using an integer there seems to be a maximum number limit i think. A guy at work built a 1 table database in access and one day we could not create any more records. We had to delete or archive a lot to free up space for more records. So i thought this maybe a way round it. Although i have not made a single table database myself.

 

[UltimateNeo]

Is a replication id a string with curly brackets? If so it needs single quotes around it.
My guess it is just a number and the brackets are only formatting and do no really exist. Get rid of brackets.

yeah it has curly brackets i couldn't get '' or removing the brackets to work so i changed the way i did it with recordsets instead and finally gt it working.

 

[CJ_London]

using an integer there seems to be a maximum number limit i think.

Integers have a maximum of circa 64,000 values. Longs (which is what an autonumber is) has a maximum of 2 billion. So if you were using an integer as a PK I can see you could go out of range. But with a long, i very much doubt you could store 2 billion records in an access db.

GUIDs are used in global corporate systems since they could exceed 2 billion records.in access they were used for replication- a means of combining data from separate dbs into a master -a typical scenario being a sales person out on customer visits with his local copy of the db and then updating the master db on their return to the office. Very useful in the days before fast internet and WiFi but not really relevant today although I can envisage the odd scenario where it would be useful. Its really only still in modern versions of access for backwards compatibility

 

[theDBguy]

I got it working using recordsets, the reason i used a replicationID was because you can have infinite records whereas using an integer there seems to be a maximum number limit i think. A guy at work built a 1 table database in access and one day we could not create any more records. We had to delete or archive a lot to free up space for more records. So i thought this maybe a way round it. Although i have not made a single table database myself.

GUID might allow you to have "infinite" records, but Access itself won't. It has a physical file size limit of 2GB. So, even if you could technically still add records, if the size of the db file hits this limit, you won't be able to add more records.