[Access 2k3 + VBA] SQL UPDATE add pk_id instead of customers name

Hi,

I have a product table with a bound combobox to customer name table;, and when I try update:


	Code:
	UPDATE product
SET customer_name_clone=customer_name;

the update function add customer PK_ID instead of customers name!!!

How can I fix this, please?

Regards


Post your answer or comment

comments powered by Disqus
I am new to Access, and so I don’t understand much technical language.
I am running a report based on a query. The query is working perfectly, and shows the text from the tables I am querying. The report, however, is showing the ID instead of the text for a couple of the fields.
This is very strange, because out of five fields the report is reporting on from the query, two show the text just like the query, and the other three show the IDs (linked way back to their original tables) instead of the text. I thought that if the query is reporting the text, so should the report.
I need the report to show the text for all of the fields. Can someone please guide me on how to do this? I am sure there is a setting I need to switch, but I don’t know where to go for that. I do not know programming language, and so I am bounded by the options in Access.
Thank you!

Got a little problem. I'm experimenting with Crystal because Access is splitting my data. Here is the set-up:

Report Header with company logo
Detail Section with 9 subreports (each has their own 'detail section').
Report Footer

The problem is with some of the subreports. When I insert the Values field, instead of displaying Biodegradability, 5 Days; Biodegradability 13 Days; Biodegradability 20 Days; etc, it displays 7.00; 6.00; 5.00; etc. I tracked this down and these are the ID values that correspond to the values:

ID Value
7.00 Biodegradability 5 Days
6.00 Biodegradability 13 Days

The ID field is the Key field. This table is linked to another so it needs the key.

I have two questions: Why is it using the ID instead of Values and how do I fix this?

Thanks for your help!

Could someone please take a look at this db and tell me why I am having the problem of the report showing id values instead of the names related to them. I have tried everything I can possibly think of and I must be missing something very little.

Thank you in advance.

I have a report that I am generating from a table which thru a filter show tasks that are past due. The text boxes on the report are:

Tasks.Project, Tasks.Title, Tasks.Assigned To, and Tasks.Date Due

The report does what I want it to do except that it shows a number (Tasks.ID) instead of the Project. I can't figure out why this is happening or how to stop it. Please help!

Hella all,

I'm new to this forum so I say hello to all of you

I have a little problem..

I have a table wich stores information wich comes from a combobox.
The problem is that it saves the combobox ID instead of the text.

This is the row source of the combobox

SELECT rapporteur.ID, rapporteur.rapporteur FROM rapporteur;

Could somebody tell me how I can store the rapporteur.rapporteur in a other table instead of the rapporteur.ID?

thx a ton in advance

Hi All,

Please could someone kindly help me with this issue. I'm sure it is something simple, but I don't know what I'm doing wrong.

All I want to do is update a table with the Windows Username of the person importing a spreadsheet.

I have imported the spreadsheet successfuly into a temporary table and now want to update it. The code just doesnt work.


	Code:
	Public Function UpdateDateAndBy()
    Dim SQL As String
    
    Dim WINUSERNAME As String
    
    WINUSERNAME = Environ("UserName")
    
    SQL = "UPDATE tblMainData SET ImportedBy=" & WINUSERNAME
    
    DoCmd.RunSQL SQL
End Function

Any help is greatly appreciated.

Thanks.
Ivan

Hello,

When I add a butten using the wizard Access 2007 adds a macro instead of vba code.
How can I get the good old VBA code???

Thanks for your help in advance

Howzit

I am trying to convert an update query into vba. This update query includes the use of the DMIN function. The query works, but I can't get the vba method to use.

The query is:


	Code:
	UPDATE tblSOVStaging SET tblSOVStaging.SOVYear = DMin("[SOVYear]","tblSOV","[AuditNo]='" & tblSOVStaging!AuditNo & "'");

My current vba method is


	Code:
	strSQL = "UPDATE tblSOVStaging "
strSQL = strSQL & "SET tblSOVStaging.SOVYear = DMin(" & Qte & [SOVYear] & Qte & "," & Qte & tblSOV & Qte & "," & Qte &
[AuditNo] & Qte & "= tblSOVStaging!AuditNo );"
CurrentDb.Execute strSQL

QTE is a Public Const """"

I have also tried the chr(34).

The error message I am getting is...

Quote: Microsoft Access can not find the field 'l' referred to in your expression The fields and the tables are correct, but I can't seem to get the syntax corect.

Any help would be appreciated.

Hello friends,

Can any one please pass on the detailed comparison of Access 2007 and SQL 2005/2008 with the limitations of Access over SQL.

Thanks in advance.

Morning all,

I'm having real fun with Access '07.... not.

I've designed a report that effectively prints a list of bookings based on a user-input date range, grouped by the staff member that's booked to lead. All well and good (did have a minor problem with a multiple value list but sorted that), except that when printed/viewed etc, instead of printing the [Staff Member] field it puts their [ID] field in instead... any ideas what I can do to correct this?

I did try removing the field from the report as it was a text box and re-inserted as a Combo box, but it stopped working altogether and duplicated all the bookings for each staff member, still only printing the ID instead of a name!!!

Thanks in advance, it's giving me a headache!

Barry

I have a form with cascading combo boxes. I need to be able to update my date field when an employee completes a training document. Everything works to pull the records up, but when I input a date it creates a new record instead of just entering the date in the current record. My "allow additions" is set to no, (I have tried it both ways) .

Any ideas on how to fix this?

Thanks for your help

Toni

Hey Guys and Gals. I am new to this forum and have a question that has been troubling me all day. I did plenty of searching and I couldn't find any answer, although it may be a simple one.

So I have a combo box in a form that refers to a table that has a long list of companies. When I test out the combo box and pick one of the companies, it adds that company (with a new foreign key ID) as another record in the company table. So that company is now in the table twice. Is there any way to use the combo box to refer to a record that is already there instead of creating a new one off of your selection?

I am sorry if this is a noob question... I just need it to do this so that the existing primary key for that company is carried over as a primary key for the relating table... not create a whole new record like it is. Thank you so much for your help!

Windows7 64bit
Access 2010 32bit

I have a very odd problem. When trying to open a report that worked wonderfully last week, there are suddenly no results. I discovered the reason was that somehow DATE is getting translated as 2010-06-22 instead of 2012-03-13, which is today's date. I tried "=Date()" in the control source of a control and that worked properly. I changed the date on my computer, and the report still translates Date as 2010-06-22. The date on the server that the database is held on is correct. I've moved the database to my local machine and the problem persists. The code is below.

DoCmd.OpenReport "rptLotData", acViewReport, , "([qryLotDataConglomerateWithTotals].[FormattedDate] Between #" & Date & "# And #" & DateAdd("m", -1, Date) & "#) And ([FirstOfModel] Like ""*" & [VINCodeReportSortByUnitField] & """" & " Or [FirstOfModel] Like """ & [VINCodeReportSortByUnitField] & " *"")"

I've gotten around it by making a control named TodayField with control source "=Date()" and changing Date to [TodayField] in the VBA, but that doesn't seem like it should be a necessity. Any ideas?

I have created a database for management of our paper records. It conists of a series of tables which record file names (eg PRIMARY, SECONDARY, TERTIARY, SUB TERTIARY). These tables are then used as look ups to create the RECORDS table which records the file names. I want to print out sticky labelsfrom the RECORDS table to stick on the hard files. I used the Label Wizard for this. The problem is that instead of showing the Field Names the Report generated by the Label Wizard shows ID numbers. How do I fix this?

Palladio

I have a table which contains employee names (NAMES). NAMES is linked to several other tables, for instance, ACTIONS records which employee took an action and uses NAMES to provide a lookup box.

I can design my NAMES table in one of two ways: ID|Name, or just Name. What are the pros and cons of each way? There's pretty much no chance I'll ever have 2 employees with the same name. I feel like using IDs is the best way, but can't really justify why it's worth the hassle of the extra field. My database is not huge - probably no more than 500,000 records at its largest. Storing the ID instead of the name means the NAMES table has to be pulled in to queries to show who took an action, instead of seeing all the information you need when you simply open the ACTIONS table.

Hi,

I am rather bamboozled by the way my table acts...

I have a form and a table

tblInput & frmInput

There is a combo box on frmInput called cboEthnicity

This looks up tblEthnicity using this code;

SELECT [tblEthnicity].[ID], [tblEthnicity].[Ethnicity] FROM tblEthnicity;

Whenever I access tblInput and wave the mouse pointer over the Ethnicity column then an error box pops up stating that the text is invalid for the field.

I have already checked the field size and no text is longer than the allowed for the field and I am stuck.

I can on the tblInput after wading through a few error boxes select from a drop down list the same Ethnic Background e.g. White and then it accepts it fine and allows the mouse pointer over it! The only trouble then is that my frmInput then displays the ID instead of the Name of the Ethnicity...

I figure it is something to do with the columns and binding etc but I think I have tried most combinations with no success...

Anyone seen anything like this before??

Hello,

I have the following tables:

tblEmployee (one to many rel to tblEmployeeAndPipeline)
EmployeeID PK
FullName
FirstName
LastName

tblEmployeeAndPipeline (many to one rel to tblPipeline)
EmployeePipelineID PK
EmployeeID (FK to tblEmployee)
PipelineTrackID (FK to tblPipeline)

tblPipeline
PipelineID
PipelineTrackID
Comments
Status
PipelineOwnr (this is field where users populate the tblEmployee)

tblHBC (one to one rel to tblPipeline)
HBCID
PipelineTrackID PK
BCName
ChngDT

now when I try to create a query to see who the Pipeline Ownr is, I get the numbers instead of name. When I try to bring in the tblEmployee and tblEmployeeAndPipeline, I get no results. Is there something wrong w/ my relationships or how I'm querying it?

So I have a combo box for techs and a mail function built to pre-populate information in an email. What happens is it pulls the tech (or Assigned To) record ID value, not the name, i.e.

...closed by 7... instead of ....closed by FirstName LastName....
I have tried to use strHelpDesk = Me.[Assigned To].column2 to no avail.

Here is my code:

'-- Helpdesk employee ticket assigned to
strHelpDesk = Me.[Assigned To]

'--Email subject and details
stSubject = "Ticket: " & stTicketID & " has been closed"
stText = "Ticket number: " & stTicketID & " for " & stDetails & " was closed by " & strHelpDesk & " on: " & RecDate & Chr$(13) & Chr$(13) & "If this ticket was closed in error, please contact the Help Desk to re-open ticket." & Chr$(13) & "This is an automated message." & _ " Please do not respond to this e-mail."

My Combo Box for Assigned To:
SELECT Techs.ID, Techs.[FirstName] & " " & Techs.[LastName] AS Expr1 FROM Techs ORDER BY Techs.[FirstName];

Hello.
I found one thread that talked about this issue but there was no resolution.

I have my main table that has lookup field linked to another table. When data is entered, the main table shows the ID of the record instead of the actual data. How can I fix this? Thanks.

Hello everyone,

I have a problem which I don't know if it's basic but i don't find any solution (I have to say that I'm a noob in this program)

I have 3 tables

The 1st one (Company) I have a list of companies.
The 2nd one I have a list of parts and it have a column with lookup function (I made it using lookup wizard) which shows the list of companies from (Company table) to choose one company.
The 3rd one table is where I have the problem. As it can be seen, when I want to choose one part from (Part table), The combobox shows the ID instead of the name of the company.

How can I solve it?

Thanks a lot!

Bye! Attached Thumbnails       Reply With Quote 01-13-2013, 12:42 PM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,107 I never set lookups in tables: http://access.mvps.org/access/lookupfields.htm

Also don't work directly with tables and queries. I build forms to interface with data.

Review http://datapigtechnologies.com/flash...combobox3.html

Hello everyone,

Hope someone can help.
I build a combo box in a form that selects customers names from the customers table. On the form, no problems. However, if I look in the table, the customer's id is stored in the field, not the name.
Of course, that has an impact on the queries and reports extracted from that table in that all show customer id instead of name.

Your help would be much appreciated, thank you.

i'm having a problem storing the data from my combo box onto the appropriate table. which property is this on the list of properties for the combo box? right now it is storing the autonumber(ID#) instead of the name associate with the number. does this have to do with the row source? i need to fix this in order to print reports and store the data on tables more easily. thanks for your time & help.
Data Slave.

I setup a combo box where I specify the data to be pulled from a table. The table just contains the primary key (id) and a name, which is what I want loaded in the combobox pulldown.

When I run the form, the names appear but when I select, the id is written to the table instead of the name.

What settings am I missing and why is it taking the id instead of the name ?

Hi Folks

I have office 2010. In my Excel Macro, I added reference to DAO 3.6 object library. My goal is to be able to update a record in a table or add a record to the end of the table.

I et an error as soon as I reach this line

Dim db As Database, rs As Recordset, r As Long
'
Set db = OpenDatabase("C:Documents and Settingssome.accdb")


The error is un-recognize database format

I need to do this via vba as opposed to manual. Any help with example on how to access 2010 db and update records will be greatly appreciated.

Mike


Not finding an answer? Try a Google search.