How to Display A Value in the Form? (1 Viewer)

[JithuAccess]

Hello Guys,

In my Table I have the following Fields:

ID
FName
Notes

This is my Table:

I have created a Form and gave This Table as Record Source.

This is my Form:

Now in the New Data Entry, I want to Display "Mark" in the FName Field of the Form when a user Enter "100" and "John" when a user enter "200"and I want to save this Record in my Table. I am not sure this is possible because I have given Table Fields as Control Source of the Text Boxes and even if I create a query to display the FName in the Text Box of the Control Source Property of my Text Box it will be an Unbound Object and I don't think that value will be saved in the Table.

Could you please tell me how to do this?

Thanks

 

[theDBguy]

Hi. Not sure I follow... Why would you want the user to enter an existing record and create a duplicate record in the same table?

Maybe you're talking about wanting to use a Combobox?

 

[JithuAccess]

Hi. Not sure I follow... Why would you want the user to enter an existing record and create a duplicate record in the same table?

Maybe you're talking about wanting to use a Combobox?

Hi,

Apologies for not making this clear. Let's take like this. We have just 2 IDs and 1000 Records. When a user enter an ID (e.g. 100) I want to just display the Name so that when a user doing the data Entry there won't be a repetition of entering the Name (i.e. Mark) and avoiding any typos. So when a user enter the 1001 Record, he is just entering the ID 100 and the name will be display automatic. In our previous case, all the 1000 Records, a user has to Enter the Name either Mark or John and I want to simplify this.

Thanks

 

[Gasman]

I would use a combox for Fname, then all would be needed is an M or a J.?
The same could be applied with ID, with a 1 or 2, but I think more user friendly to use the Fname. Then you can assign ID to the other column that would hold the ID in the rowsource

 

[JithuAccess]

I would use a combox for Fname, then all would be needed is an M or a J.?
The same could be applied with ID, with a 1 or 2, but I think more user friendly to use the Fname. Then you can assign ID to the other column that would hold the ID in the rowsource

Hi,

Thanks a lot for your reply. Let me explain you my Table and Form.

These are the values in my Table:

Here, when a user enter an ID e.g. 3619 I want to show the Name, DoB, Facility Name, City, State and Country in my Text boxes. Because all these values are same. So I don't want a user to manually enter these fields repeatedly , to make Data Entry more easy and error free.

This is my Form:

On the next Entry, when a user enter "8473" in the ID, I want to display the below values in the Text boxes of the Form Automatically, i.e. the user is not going to enter these values:

Because these fields will never change, even if we have 10,000 entries for this ID.

Is there any way to do so?

Thanks

 

[theDBguy]

So I don't want a user to manually enter these fields repeatedly , to make Data Entry more easy and error free.

Hi. This is the part I was confused about earlier. Storing duplicate/redundant data in a table is not recommended. So, you shouldn't be doing it.

 

[JithuAccess]

Hi. This is the part I was confused about earlier. Storing duplicate/redundant data in a table is not recommended. So, you shouldn't be doing it.

Thanks a lot for your reply

 

[JithuAccess]

Hi. This is the part I was confused about earlier. Storing duplicate/redundant data in a table is not recommended. So, you shouldn't be doing it.

There are total more than 6,000 Records in this format and I can't change the current Format as this is a very time sensitive job. So I am looking for a Temporary solution. But, there isn't any way to display these values in a Text Box?

Thanks

 

[Gasman]

Yes, as I mentioned.
Bring in those fields as DISTINCT for your combo row source. (or GROUP BY)
In the AfterUpdate event of the combo, set the relevant controls to the correct column of the combo.

I still think it is more user friendly to start typing Ja than 9457 ?, but it is up to you.
The logic remains the same. BTW column numbers start at 0.

 

[theDBguy]

But, there isn't any way to display these values in a Text Box?

Of course there is. I was just saying it shouldn't be necessary. But, since your situation is different, I suppose you have no choice at the moment.

However, as I and @Gasman were saying earlier, it would make it easier/simpler to accomplish what you want, if you use a Combobox.

What about if the user wants to enter a new (no duplicate yet) record?

 

[JithuAccess]

Of course there is. I was just saying it shouldn't be necessary. But, since your situation is different, I suppose you have no choice at the moment.

However, as I and @Gasman were saying earlier, it would make it easier/simpler to accomplish what you want, if you use a Combobox.

What about if the user wants to enter a new (no duplicate yet) record?

Thanks a lot to know that thee is a way to do so. If a user enter a new ID, nothing will be displayed in the Text boxes and the user will be entering the values (like Name, DoB, Facility Name etc) just one time and if a user enter this new ID next time all these values will be displaying automatically.

Could you kindly let me know, how do I implement this in my Form? It will save a lot of time for us.

Thanks

 

[theDBguy]

Thanks a lot to know that thee is a way to do so. If a user enter a new ID, nothing will be displayed in the Text boxes and the user will be entering the values (like Name, DoB, Facility Name etc) just one time and if a user enter this new ID next time all these values will be displaying automatically.

Could you kindly let me know, how do I implement this in my Form? It will save a lot of time for us.

Thanks

Sure; but first, what did you not understand with @Gasman's instructions in Post #9? I would be telling you the same thing.

 

[JithuAccess]

Sure; but first, what did you not understand with @Gasman's instructions in Post #9? I would be telling you the same thing.

I can't use combo box please

 

[Gasman]

So use DLookup() instead. Same event, set each control to the relevant DLookUp() statement.

 

[JithuAccess]

So use DLookup() instead. Same event, set each control to the relevant DLookUp() statement.

Thanks a lot

 

[theDBguy]

I can't use combo box please

Okay, @Gasman gave you a different approach. I, however, have a question. Why can't you use a Combobox?

 

[JithuAccess]

So use DLookup() instead. Same event, set each control to the relevant DLookUp() statement.

Thanks a lot for your Suggestion.

I have use Dlookup() Function. I have enter this Function in the Control Source Property of the Fname Text

=DLookUp("[Fname","Test","ID=" & [Forms]![Form3]![ID])

Here in the criteria I just want to get the Fname from the Table Test if the value of the field "ID" is equals to the ID which I entered in the ID Text Box of this Form.

Unfortunately, I am getting an Error like this:

Thanks

 

[Gasman]

=DLookUp("Fname","Test","ID=" & [Forms]![Form3]![ID])

 

[JithuAccess]

=DLookUp("Fname","Test","ID=" & [Forms]![Form3]![ID])

Thanks a lot for your Suggestion.

I have use Dlookup() Function. I have enter this Function in the Control Source Property of the Fname Text

=DLookUp("[Fname","Test","ID=" & [Forms]![Form3]![ID])

Here in the criteria I just want to get the Fname from the Table Test if the value of the field "ID" is equals to the ID which I entered in the ID Text Box of this Form.

Unfortunately, I am getting an Error like this:

Thanks

 

[Gasman]

@JithuAccess

Do NOT go and crosspost wihout letting people who have responded know about it.

https://www.utteraccess.com/topics/2060522?post=unread

For that very reason I will bow out now.